Power Query power query

[GIPJGR]

Bonjour,

Ravi de me joindre au forum Excel Downloads, j'ai pu en parcourant les différents fils de discussion régler pas mal de problèmes dans la réalisation de fichiers excel pour m'aider dans mon métier (analyse financière).
Aujourd'hui je bloque néanmoins sur Power Query et je me permets de m'adresser directement à vous, pour voir si d'aventure une solution existerait

Pour rentrer dans le vif du sujet, je souhaite importer des données depuis Reuters vers Excel via Power Query.
Via un enregistrement de macro, j'ai pu établir un script (avec un modèle X) qui marche dans l'ensemble, cependant des bugs peuvent survenir avec d'autres entreprises et casser l'importation des données:
- quand la 2ième colonne du tableau affiche pour titre 30-Jun-2021 (l'enregistrement de macro, je l'ai fait avec un tableau dont la 2ième colonne était 31-Dec-2020)
- quand le tableau visé comporte 3 ou 4 colonnes seulement (l'enregistrement de macro, je l'ai fait avec un tableau qui comptait 7 colonnes, donc ma macro cherche des colonnes qui n'existent pas, d'ou le bug)
Quand j'essaie de comprendre ou est l'erreur, c'est souvent Refresh BackgroundQuery:=False qui est surligné.

Il faudrait à priori que je réécrive une partie du code de telle sorte que la macro soit plus "souple", qu'elle se contente d'importer les colonnes existantes du tableau, sans se soucier de leur libellé, ou du nombre de colonnes du tableau

Voila le code en question

FULL YEARS ONLY'!$H$6 fait référence à un lien URL tel que https://www.reuters.com/companies/FROB.PA/financials/cash-flow-quarterly

Dim MonLien As String

MonLien = Range("'FULL YEARS ONLY'!$H$6")



ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _

"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""" & MonLien & """))," & Chr(13) & "" & Chr(10) & " Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(Data0,{{"""", type text}, {""31-Dec-20"", type number}, {""31-Dec-19"", type number}, {""31-Dec-18"", type text}, {""31-Dec-17"", type text}, {""31-Dec-16"", type" & _

" text}, {""Trend"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type modifié"""

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _

"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _

, Destination:=Range("'FULL YEARS ONLY'!$A$1")).QueryTable

.CommandType = xlCmdSql

.CommandText = Array("SELECT * FROM [Table 0]")

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.ListObject.DisplayName = "Table_0"

.Refresh BackgroundQuery:=False

End With

Je suis certain que c'est la partie en gras qui déconne, mais comment la réecrire de telle sorte qu'elle se contente de prendre les noms des colonnes dans le tableau sans chercher à tout prix 31-Dec-20, 31-Dec-19, 31-Dec-18, etc, et sans bugger quand il n'y a pas 7 colonnes comme dans le cas initial?

En toute franchise, je démarre tout juste sur VBA, merci pour votre bienveillance et vos suggestions


Bonne soirée-)

 

[chris]

Bonjour

Ce message n'a aucune importance

De plus il n'y a que très peu de différences (2 à ma connaissance) entre la toute première version en add on pour 2010 et celle d'aujourd'hui et donc il est très rare que cela coince.

Je n'ai jamais eu le cas entre 2 versions de 365

l'actualisation ne s'enclenche pas

Une actualisation ne s'enclenche jamais seule sauf à gérer par VBA le changement de valeur de la cellule STE (en jaune).
L'as tu lancée ?

La seule erreur que tu aurais pu avoir est liée au réglage de confidentialité qu'il faut systématiquement désactiver.

Comme déjà dit et redit, on ne sait toujours pas comment tu utilises les données, si les dates sont utiles ou non....

Ta structure, sans tableau structuré, avec des tableaux les uns au-dessus des autres et juxtaposés alors que le nombre de colonnes de la source est variable, et peut-être plus tard le nombre de lignes, est déjà une mauvaise conception.

Il n'est pas certain, surtout avec 365, que la meilleure approche soit le RECHERCHEV

Mais là aussi sans info concrète... je ne vois pas comment analyser la meilleure approche.

 

[GIPJGR]

Bonsoir Chris,
Tu avais raison pour le réglage de confidentialité, c'est bien ce qui bloquait l'actualisation de la requête. Ca marche!
Comme évoqué, je vais désormais me familiariser au + vite avec POWER QUERY pour comprendre la mécanique


J'entends tes remarques sur ma structure, et mes tableaux juxtaposés
De ce que j'ai pu constater jusqu'à présent, les colonnes des 3 tableaux de chacune des 2 feuilles (Full years + quarters only) se rapportent toujours aux mêmes dates de cloture comptable. Comme pour mon exemple avec ADM, toutes les données de la colonne B se rapportent à la date de cloture 30-Jun-21, la colonne C au 31-Mar-21 etc. Effectivement, les dates de cloture sont primordiales..

Ensuite le problème de ces données, c'est qu'elle sont sur 12 mois dans les colonnes de l'onglet full year, mais sur 3 mois dans les colonnes de l'onglet quarters

Si je veux faire une situation intermédiaire sur 3 mois, OK c'est le Q1
Si je veux une SI sur 6 mois, alors Q1+Q2
Si je veux une SI sur 9 mois, alors Q1+Q2+Q3
Ca c'est pour la période N, et pour pouvoir reconstituer la même période de l'année N-1 et faire des comparaisons, ce sera une autre gymnastique.

Par exemple pour déterminer la valeur "revenue" de l'année N, j'aurai ensuite la combinaison de recherchev suivante:
=SI(F2="CONSO";RECHERCHEV("Revenue";'FULL YEARS ONLY'!A:N;10;FAUX);
SI(F2="Q1";RECHERCHEV("Revenue";'QUARTERS ONLY'!A:N;10;FAUX);
SI(F2="Q2";RECHERCHEV("Revenue";'QUARTERS ONLY'!A:N;10;FAUX)+RECHERCHEV("Revenue";'QUARTERS ONLY'!A:N;11;FAUX);
SI(F2="Q3";RECHERCHEV("Revenue";'QUARTERS ONLY'!A:N;10;FAUX)+RECHERCHEV("Revenue";'QUARTERS ONLY'!A:N;11;FAUX)+RECHERCHEV("Revenue";'QUARTERS ONLY'!A:N;12;FAUX);
SI(F2="half year cad Q1+Q2";RECHERCHEV("Revenue";'QUARTERS ONLY'!A:N;10;FAUX);"bug")))))

C'est lourd, mais ça tient. Si tu vois une approche alternative, je suis tout ouïe.

Bonne soirée

Cordialement

 

[chris]

Bonjour

On peut croiser le tableau, ce qui te permettra de récupérer plus facilement mais il y a un souci sur Minority Interest : on a 2 valeurs pour chaque période.
On les somme ?
On supprime cette catégorie ?

Tes recherches ne semblent pas traiter l'année en tant que critère précis ce qui me semble risqué au fil du temps...
Et ton exemple ne semble pas cohérent avec ces formules puisque je n'y ai pas 10 colonnes

 

[GIPJGR]

Bonsoir Chris,
Oui c'est vrai, l'année n'est pas traitée en tant que facteur précis, ce qui est une faiblesse. Pour ce que j'ai vu jusqu'à présent les 3 tableaux de FULL YEAR et de QUARTERS ont toujours le même format: Les données concernant une date de cloture précise tombent toujours dans la même colonne. Mais je n'ai pas à ce stade la technique pour contrôler ce point.

L'occurrence Minority interest apparait toujours 2 fois c'est vrai. Une seule rentre dans mes calculs, celle qui figure dans le bilan (et qui apparait en 2ième). Pas de pbs de ce côté, car ma macro (quand elle marche) enregistre d'abord le compte de R, puis le bilan puis la tréso. Je fais ensuite une recherchev/decaler pour éviter la 1ière occurence

Les numéros de colonne devraient plutot être 2,3,4,5,6, au lieu de 10,11,12,13,14 oui.
Mais quand la macro marche pas je fais les copier-coller moi-même (pas génial j'en conviens mais ça prend 15 secondes), et à l'arrivée il peut y avoir des cellules en format nombre, et d'autres en texte, avec des espaces insécables, etc. Je nettoie ça avec des formules substitue dans les colonnes 10 à 14

 

[chris]

Bonjour

Tu utilises toutes les années ou bien les n dernières et si oui lesquelles ?

 

[GIPJGR]

Bonsoir Chris,

Pour les données Full year, seules les 2 périodes les plus récentes sont necessaires
Pour les quarters, toutes les périodes sont necessaires pour que les additions/soustractions de recherchev qui viennent derrière soient efficaces

Désolé pour ce retour tardif, mais... il m'a fallu un peu de temps pour comprendre un peu mieux la mécanique de Power Query

Pour me faire la main (dans la mesure du possible), j'ai cherché les manips dont découlait ton code et j'arrive à ça

******************
let

Source = Excel.Workbook(File.Contents("D:\TEST POWER QUERY.xls"), null, true),

Feuil2 = Source{[Name="Feuil1"]}[Data],

#"Type modifié" = Table.TransformColumnTypes(Feuil2,{{"Column1", type text}}),

#"Colonnes renommées" = Table.RenameColumns(#"Type modifié",{{"Column1", "URL"}}),

#"Fonction personnalisée appelée" = Table.AddColumn(#"Colonnes renommées", "Data", each Traiter_URL ()), #"Autres colonnes supprimées" ...t tes éventuelles corrections) Cordialement,

 

[chris]

Bonjour

Pourquoi partir d'un fichier externe ? Il est plus simple de partir du fichier qui contient les paramètres

j'ai croisé les données et filtré dynamiquement sur les 2 dernières années

 

[GIPJGR]

Bonsoir Chris,
Un grand merci pour ton aide, je vais prendre le temps de décortiquer tout ça et le mettre en pratique

Je reviens ensuite vers toi

Encore merci