Aandelenkoersen importeren vanuit Yahoo Finance met Power BI. (In 9 stappen)
Een van de deelnemers aan mijn Power BI Training had onlangs een goede vraag, ze wilde een Power BI rapport samenstellen met daarin aandelenkoersen.
Het probleem waar ze tegen aanliep is dat de meeste gratis websites slechts de gegevens van de laatste handelsdag tonen. Bij andere websites, zoals bijvoorbeeld bij Yahoo Finance kun je wel historische gegevens downloaden, maar die zijn dan altijd voor één enkel aandeel.
Je wil natuurlijk niet voor ieder aandeel een aparte query moeten maken.
Na behoorlijk wat gepuzzel (en natuurlijk wat hulp van google) denk ik dat ik een goede oplossing gevonden heb.
In dit artikel beschrijf ik stap voor stap hoe je Power Query kan gebruiken om via Yahoo Finance alle gewenste koersdata te importeren.
Ik gebruik hier als voorbeeld de AEX maar deze oplossing werkt uiteraard ook met buitenlandse beurzen, wisselkoersen en cryptovaluta’s. Dus ik denk dat dit voor een hoop (hobby)beleggers interessant kan zijn.
Stap 1: De Yahoo finance link:
Om te beginnen ga ik naar de Yahoo website om de download opties te bekijken.
De data komt vanaf de website https://finance.yahoo.com/ op deze website zijn aandelen op te zoeken via de zoekbalk. Ik zocht hier op Ahold.
Hierna klikte ik op het onderdeel “historical data”. In dit onderdeel kan je filters op datum, frequentie en categorie aanbrengen. Hier zit ook een downloadknop
Via de rechtmuisknop kan ik de link uit die downloadknop kopiëren.
In dit voorbeeld is de link:
https://query1.finance.yahoo.com/v7/finance/download/AD.AS?period1=1599260616&period2=1630796616&interval=1d&events=history&includeAdjustedClose=true
Deze link bevat een aantal onderdelen. Na een korte zoektocht op google ontdekte ik hier het volgende over.
- Het eerste deel (tot en met /download/) is bij ieder aandeel gelijk.
- “AD.AS?” is hier de code van het aandeel (Het ticker symbol).
- Period1=1599260616 is de begindatum (in Unix epoch tijd)
- period2=1630796616 is de einddatum.
- interval=1 is de frequentie (dagelijks, wekelijks etc).
- events=history is de data categorie.
- includeAdjustedClose=true bepaalt of deze column in de dataset komt.
Dit soort URL parameters vind je terug bij heel veel verschillende websites en web services. Al gebruiken verschillende websites natuurlijk verschillende parameters en datatypes. Iets dat hier bijvoorbeeld opvalt is dat de begin- en einddatum niet direct als datum te herkennen zijn.
In Power Query ga ik deze parameters gebruiken om verschillende aandelen te importeren en de periode aan te passen.
Stap 2: De basisquery maken:
In een nieuw Power BI bestand maak ik een query naar de link.
Ik hernoem daarna eerst de query, ik noem hem hier “Ophalen”.
Wat hier opvalt is dat de getallen onmogelijk groot zijn. Dit komt omdat deze query in de stappen op mijn computer automatisch data typen heeft toegevoegd. Aangezien deze data een Amerikaanse opmaak heeft (punten als decimaaltekens) gaat dit hier niet goed.
Daarom verwijder ik deze stap. Deze aanpassing komt later.
Stap 3: De AEX lijst importeren:
Ik wil de query uitvoeren voor meerdere aandelen. Daarom heb ik een tabel nodig met alle aandelen die ik wil toevoegen. Dit kan een eenvoudige Excel lijst zijn maar aangezien ik hier de hele AEX wil weten kan ik die informatie ook geïmporteerd worden uit een website.
Voor dit voorbeeld gebruik ik Wikipedia maar er zijn heel veel verschillende websites waar deze informatie vandaan te halen is.
Ik importeer de data vanaf het Wikipedia artikel https://nl.wikipedia.org/wiki/AEX. De query noem ik ‘Aandelen’.
NB
Hierin ontbreekt wel de weging van aandelen binnen de AEX. Die is wel terug te vinden op andere bronnen. Maar voor dit voorbeeld hoef ik de AEX stand niet te berekenen en dus is deze weging niet nodig.
Wil je deze weging wel meenemen hou er dan rekening mee dat die weging ook regelmatig aangepast wordt. Dus wellicht is het makkelijker de AEX stand dan als een aparte query te importeren.
Er is een een klein verschil in de notatie van het symbool tussen Wikipedia en Yahoo. Bij Yahoo wordt dit gevolgd door de code AS (Amsterdam). Deze aanpassing bereik ik hier makkelijk op via
Transform → Format → Add Suffix
Stap 4: Symbool Query:
Bovenstaande query wordt mijn dimensietabel met informatie over de aandelen. Ik wil hieruit een nieuwe query maken met de “ticker” symbolen.
Dit doe ik door de kolom in de aandelen query aan te klikken en te kiezen voor Add as New Query.
Ik krijgt hierdoor een lijst met alle aandelen symbolen. Via de knop To Table maak ik hier een tabel van.
Ik noem deze nieuwe query koersen en pas het datatype aan.
Stap 5: Een Power Query parameter aanmaken voor het aandeel:
In de volgende stappen ga ik de “Ophalen” Query uitvoeren voor iedere rij van de “Koersen query”. Iedere keer met een ander aandeel in de link.
Hiervoor moet ik de ophalen query in een functie veranderen en een parameter maken voor de ticker symbool waarde.
Ik maak eerst een parameter aan via de Manage Parameters knop of via rechtermuisknop op het Query’s paneel.
Stap 6: De parameter gebruiken in Power Query:
Ik wil deze Parameter nu gebruiken in de M code van de “Ophalen” query. Hiervoor gebruik ik de Advanced Editor.
Tip: Sla het bestand eerst op. Het is heel namelijk makkelijk een M-code te verpesten.
Ik moet deze statische link nu splitsen in een statisch en dynamisch gedeelte. Hier wordt het een beetje lastig.
Ik dacht eerst aan een oplossing door de M code te splitsen en de parameter erin te voegen.
Dit leek te werken. In Power BI desktop wordt de Query netjes ververst en werkt alles prima. Maar toen ik deze oplossing testte op de Power BI service merkte ik dat hij daar niet werkt.
Dit komt omdat de Power BI service eerst een test uitvoert met het statische gedeelte van de link. Dit is hier
https://query1.finance.yahoo.com/v7/finance/download/
Dat is helaas hier geen geldige link. Waardoor de query een foutmelding geeft.
Ik los dit op met een zogenaamd “Relative path” optie in de M code.
De link moet gesplitst worden in een statisch deel voor het vraagteken en een dynamisch deel dat na het vraagteken komt.
In de oorspronkelijke link staat het vraagteken direct na het aandelen symbool. Dat gaat hier niet werken, want juist het aandelen symbool moet veranderen, maar gelukkig werkt hij ook als een extra vraagteken toegevoegd wordt direct na het statische gedeelte.
Ik ga nu in de M code voor de source stap een extra variabele invoegen die de oorspronkelijke link bevat.
Let op: Hier gebruik ik dus “?AD.AS?” in plaats van “AD.AS?”
De source stap splitsen we vervolgens op in een verwijzing naar de link stap en een “Relative Path”.
Door deze aanpassing kan de Power BI service met een geldige link testen, terwijl via de Relative Path optie deze onderdelen ook vervangen kunnen worden door parameters.
Stap 7: Een functie maken van de Query:
Ik moet nu de “Ophalen” query omzetten naar een functie. Dit kan met de rechtermuisknop → Create Function.
Ik moet er ook nog voor zorgen dat de “Ophalen” query niet als tabel getoond wordt in Power BI.
Dit doe ik door de “Enable Load” functie uit te zetten.
Stap 8: De functie uitvoeren:
Ik ga nu de gemaakte functie uitvoeren voor iedere rij van de “Koersen” query. Ik ga hier naar de koersen query en kies voor Add Column → Invoke Custom Function.
Hier geeft ik aan dat de “Ophalen” query uitgevoerd moet worden voor iedere rij van deze tabel. Waarbij de parameter bepaald wordt door de waarde in de bestaande kolom “Ticker Symbool”.
Nu kan ik de nieuwe kolom openklappen en de Query verder samenstellen.
- Vervang ik de punten in de getallen met komma’s om een juist decimaal teken te krijgen.
- Zorg dat alle kolommen correcte datatypes hebben.
Hiermee hebben ik een query die koersinformatie ophaalt van alle aandelen in de AEX.
Stap 9: Begin- en einddatum instellen:
Hoewel de query’s nu lijken te werken zijn ze nog niet af. Het “Relative Path” in de “ophalen” query bevat namelijk twee periode filters.
De parameter period1 is het beginpunt en period2 is het eindpunt.
De notatie is hier in Unix Epoch opmaak. Dit is een manier waarop sommige websites omgaan met datum en tijd notaties. Dit getal krijg je door het aantal seconden te berekenen dat een datumtijdwaarde hoger is dan 00:00:00 GMT op 1 januari 1970.
Via een webtool ( https://www.epochconverter.com/ ) kan ik nu zien dat de huidige selectie gelijk is aan:
Period1 = 1599260616 = 04-09-2020 11:03:36 PM
Period2= 1630796616 = 04-09-2020 11:03:36 PM
Deze periode is statisch in de link, de data zal dus altijd alleen maar in deze periode vallen. Uiteraard wil ik dat niet, want dan wordt de query nooit echt ververst. Ik wil juist dat Period2 altijd de huidige datumtijd pakt.
Het beginpunt hangt meer af van wat je in een rapport wil. Ik wil hem hier laten beginnen op 1 januari 2015, maar misschien is het handig deze datum makkelijk aanpasbaar te maken, mocht ik me bedenken.
Ik moet dus twee dingen berekenen.
- De Unix epoch waarde van de huidige datumtijd
- De Unix epoch waarde van 1 januari 2015.
Deze zijn te verkrijgen zijn via iets complexere M functies.
Einddatum
Ik ga zowel voor de begin als de einddatum als nieuwe query maken. Ik kies eerst voor een blanco query die noem ik “Einde”.
Om de huidige GMT waarde te krijgen gebruik ik de functie DateTimeZone.FixedUtcNow()
De uitkomst is hier een “date/time/timezone” datatype. Ik wil hier de datumtijd waarde van de “epoch” (1-1-1970 00:00:00) aftrekken.
In de M taal is het juiste datatype erg belangrijk instellen. Je kan twee verschillende datatypes bijvoorbeeld niet zomaar van elkaar aftrekken. Ik moet dus eerst de functie aanpassen zodat het een datetime als resultaat geeft. Ik pas deze functie daarom aan met de functie DateTime.From()
Van deze functie moet ik de Unix epoch begintijd aftrekken.
De uitkomst is nu een duration datatype. Dit wil ik omzetten in het aantal seconden door deze code te combineren met de functie Duration.TotalSecond.
Dit is nu nog een decimaal getal deze kan ik aanpassen met de Int32.From functie (Unix Epoch is standaard een Int32), hierdoor wordt het resultaat een geheel getal.
Het resultaat zal dus bij iedere verversing de huidige UNIX epoch waarde berekenen. Bijna precies, want in Power BI desktop zit er nog wel een foutje in. Omdat hij hier van 1-1-1970 uitgaat van de systeemtijd van mijn computer (Nederlandse tijd).
Dat wordt echter opgelost als ik de data via de Power BI service publiceer en ververs. Daar wordt namelijk altijd de UTC tijd gebruikt.
Begindatum
Voor de begindatum maak ik eerst een parameter aan. Zodat deze later makkelijker aan te passen is.
Ik kan nu de Power Query van de einddatum kopiëren en de DateTimeZone.FixedUtcNow functie vervangen door de parameter.
Het is hierna alleen nog een kwestie van de begin- en eindquery gebruiken in de M code van de “Ophalen” query. Hier moet dit wel een text datatype krijgen, daarom gebruik ik hier de functie Text.From.
Nu kan ik de Query’s uitvoeren en heb ik bij iedere verversing de meest recente data.
Hierna kan ik het verder naar wens gaan uitwerken met een datumtabel, relaties, metingen en visualisaties.
Alweer een mooi voorbeeld van de flexibiliteit en kracht van Power Query als ETL tool. Ik hoop dat jullie er wat aan hebben!
Een voorbeeld van het rapport:
