SOLVED

Retrieveing INSEE data in Excel via Power query

%3CLINGO-SUB%20id%3D%22lingo-sub-2592108%22%20slang%3D%22fr-FR%22%3ERetrieveing%20INSEE%20data%20in%20Excel%20via%20Power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2592108%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%20I'm%20looking%20a%20way%20to%20connect%20INSEE%20data%20in%20Excel%20using%20Power%20Query%20but%20I%20don't%20understand%20how%20to%20connect%20Power%20Query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20use%20VB%20code%20I%20correctly%20get%20data%20in%20imported%20in%20an%20excel%20sheet%20but%20I%20would%20prefer%20to%20get%20a%20connected%20source%20%3A%20any%20idea%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVB%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Series()%20%3CBR%20%2F%3E%20Dim%20IdBank%20As%20Variant%2C%20i%20As%20Byte%3C%2FP%3E%3CP%3EIdBank%20%3D%20Array(%22010534284%22)%3C%2FP%3E%3CP%3EFor%20i%20%3D%200%20To%20UBound(IdBank)%20%3CBR%20%2F%3E%20Import%20IdBank(i)%20%3CBR%20%2F%3E%20Next%20i%20%3CBR%20%2F%3E%20End%20Sub%3C%2FP%3E%3CP%3ESub%20Import(Idbk%20As%20Variant)%20%3CBR%20%2F%3E%20Dim%20Site%20As%20String%2C%20Sht%20As%20Worksheet%3C%2FP%3E%3CP%3ESite%20%3D%20%22%3CA%20href%3D%22https%3A%2F%2Fbdm.insee.fr%2Fseries%2Fsdmx%2Fdata%2FSERIES_BDM%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fbdm.insee.fr%2Fseries%2Fsdmx%2Fdata%2FSERIES_BDM%2F%3C%2FA%3E%22%20%26amp%3B%20Idbk%3C%2FP%3E%3CP%3Eapplication.DisplayAlerts%20%3D%20False%20%3CBR%20%2F%3E%20Set%20Sht%20%3D%20Sheets.Add(%2C%20ActiveSheet)%20%3CBR%20%2F%3E%20Sht.Name%20%3D%20Idbk%20%3CBR%20%2F%3E%20With%20Sheets(Idbk)%20%3CBR%20%2F%3E%20ThisWorkbook.XmlImport%20Url%3A%3DSite%2C%20ImportMap%3A%3DNothing%2C%20Overwrite%3A%3DTrue%2C%20_%20%3CBR%20%2F%3E%20Destination%3A%3D.Range(%22A3%22)%3C%2FP%3E%3CP%3E.Range(%22A1%22).Value%20%3D%20%22Update%20of%20%3A%22%20%3CBR%20%2F%3E%20.Range(%22A1%22).HorizontalAlignment%20%3D%20%3CBR%20%2F%3E%20xlRight.Range(%22B1%22).Value%20%3D%20Format(Now()%2C%20%22dd%2Fmm%2Fyyyy%22)%20%3CBR%20%2F%3E%20.Range(%22C1%22).Value%20%3D%20Format(Now()%2C%20%22hh%3Ann%22)%20%3CBR%20%2F%3E%20End%20With%20%3CBR%20%2F%3E%20Application.DisplayAlerts%20%3D%20True%20%3CBR%20%2F%3E%20Set%20Sht%20%3D%20Nothing%20%3CBR%20%2F%3E%20End%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2592108%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

 

 

2 Replies
best response confirmed by Cyssou (New Contributor)
Solution

@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.

Screenshot 2021-08-01 at 08.31.06.png

@Riny_van_Eekelen thanks this is really helpfull !!