Forum Discussion

Cyssou's avatar
Cyssou
Copper Contributor
Jul 27, 2021
Solved

Retrieveing INSEE data in Excel via Power query

Hi I'm looking a way to connect INSEE data in Excel using Power Query but I don't understand how to connect Power Query

 

If I use VB code I correctly get data in imported in an excel sheet but I would prefer to get a connected source : any idea ?

 

VB code :

 

Sub Series()
Dim IdBank As Variant, i As Byte

IdBank = Array("010534284")

For i = 0 To UBound(IdBank)
Import IdBank(i)
Next i
End Sub

Sub Import(Idbk As Variant)
Dim Site As String, Sht As Worksheet

Site = "https://bdm.insee.fr/series/sdmx/data/SERIES_BDM/" & Idbk

Application.DisplayAlerts = False
Set Sht = Sheets.Add(, ActiveSheet)
Sht.Name = Idbk
With Sheets(Idbk)
ThisWorkbook.XmlImport Url:=Site, ImportMap:=Nothing, Overwrite:=True, _
Destination:=.Range("A3")

.Range("A1").Value = "Mise à jour du :"
.Range("A1").HorizontalAlignment = xlRight
.Range("B1").Value = Format(Now(), "dd/mm/yyyy")
.Range("C1").Value = Format(Now(), "hh:nn")
End With
Application.DisplayAlerts = True
Set Sht = Nothing
End Sub

 

 

  • Cyssou Perhaps the attached file helps you to get started with PowerQuery.

     

    Connecting to the site with the specified Idbank-code produces a list of indices for the past 17.5 years, but perhaps I didn't find all the data "hidden" in the XML source. Enter an Idbank-code on the first sheet. On the data ribbon, press Refresh All and the data will be updated in the table on the Data sheet.

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    Cyssou Perhaps the attached file helps you to get started with PowerQuery.

     

    Connecting to the site with the specified Idbank-code produces a list of indices for the past 17.5 years, but perhaps I didn't find all the data "hidden" in the XML source. Enter an Idbank-code on the first sheet. On the data ribbon, press Refresh All and the data will be updated in the table on the Data sheet.

Resources