Power BI Tip: Aandelenkoersen importeren naar Power BI vanaf Yahoo Finance

Een van de deelnemers aan de Power BI Training stelde onlangs een goede vraag aan trainer Jeroen. De deelneemster wilde een Power BI rapport samenstellen met daarin aandelenkoersen.

Het probleem: data van slechts 1 handelsdag of van slechts 1 aandeel

Het probleem waar ze tegen aanliep is dat de meeste gratis websites slechts de gegevens van de laatste handelsdag tonen. Of anders, zoals bijvoorbeeld 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 maken.

De oplossing: Power Query als webscraping tool

Na behoorlijk wat gepuzzel heeft Jeroen een goede oplossing gevonden. In dit artikel legt hij uit hoe je Power Query kan gebruiken als zogenaamde webscraping tool om via Yahoo finance alle gewenste koersdata te importeren. Als voorbeeld wordt de AEX gebruikt, maar deze oplossing werkt uiteraard ook met buitenlandse beurzen, wisselkoersen en cryptovaluta’s . Dus ook interessant voor (hobby)beleggers! Bekijk onderstaand stappenplan hoe jij deze oplossing in kan zetten.

Stap 1:

• Ga naar https://finance.yahoo.com/ Hier zijn aandelen op te zoeken via de zoekbalk. In onderstaand voorbeeld werd gezocht op ‘ahold’.

• Klikte vervolgens op de tab “Historical data”.

• Hier kan je filteren op datum, frequentie en categorie. En je kan deze data ook downloaden, via de Download knop. Onder de downloadknop zit de link die je nodig hebt.

 

• In dit voorbeeld is de link onder de Download knop: https://query1.finance.yahoo.com/v7/finance/download/AD.AS?period1=1599260616&period2=1630796616&interval=1d&events=history&includeAdjustedClose=true
  • Hoe kom je aan deze link? Houd de muis op de ‘Download’ knop. Klik op de rechtermuisknop en selecteer ‘Koppeling kopiëren’. Deze kan je nu plakken in Word, Excel etc en bekijken.
• De link bevat een aantal onderdelen. Na een korte zoektocht op google ontdekte ik hier het volgende over.
  • Het eerste deel 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 URI parameters vind je terug bij veel verschillende websites en webservices. Echter gebruikt bijna elke website andere 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 URI parameters gebruiken om verschillende aandelen te importeren en de periode aan te passen.

Stap 2:

• Maak in een nieuw Power BI bestand een query naar de link

• Hernoem de query naar “Ophalen”.

• Wat hierboven opvalt is dat de getallen onmogelijk groot zijn. Dit komt omdat deze query in de stappen bij mij automatisch data typen heeft toegevoegd. Dit komt omdat deze data een Amerikaanse opmaak (punten als decimaaltekens) gebruikt.Daarom verwijder ik deze stap. Deze aanpassing komt later.

Stap 3

• Je gaat nu de AEX lijst importeren
• De query wordt uitgevoerd voor meerdere aandelen. Daarvoor heb je een tabel nodig met alle aandelen die je wilt gebruiken. Dit kan een Excel lijst zijn. Maar als je de hele AEX wilt gebruiken kan 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’.

Let op: Hierin ontbreekt wel de weging van aandelen binnen de AEX. Die is wel terug te vinden op andere bronnen. Maar voor dit voorbeeld hoef je de AEX stand niet te berekenen en dus is deze weging niet nodig. Wil je deze weging wel meenemen? Houd 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 hier een klein verschil in de notatie van het symbool. Bij Yahoo wordt dit gevolgd door de code AS (Amsterdam). Deze aanpassing bereik je hier makkelijk op via Transform à Format à Add Suffix

Stap 4:

• Bovenstaande query wordt de dimensie-tabel met informatie over de aandelen. Je gaat hieruit een nieuwe query maken met de “ticker” symbolen
• Dit realiseer je door de kolom in de aandelen query aan te klikken en te kiezen voor Add as New Query. Zie onderstaande afbeelding.
  

• Er verschijnt een lijst met alle aandelen symbolen. Via de knop To Table maak je hier een tabel van.

• Noem deze nieuwe query ‘koersen’ en pas het datatype aan

Stap 5

• Je gaat nu een parameter aanmaken voor het aandeel
• In de volgende stappen ga je de “Ophalen” Query uitvoeren voor iedere rij van de “Koersen query”. Iedere keer met een ander aandeel in de link. Hiervoor moet je de ‘ophalen’ query in een functie veranderen en een parameter maken voor de ticker symbool waarde.

• Maak eerst een parameter aan via de Manage Parameters knop of via rechtermuisknop op het Query’s paneel

Stap 6: De parameter gebruiken

• Gebruik deze Parameter in de M code van de “Ophalen” query. Hiervoor gebruik je de Advanced Editor.

Let op: 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 uittestte 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 (de zogenaamde URI) 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 6

• Je gaat nu een functie maken van de Query. De “Ophalen” query moet je omzetten naar een functie. Dit kan met de rechtermuisknop: Create Function

• Zorg dat de “Ophalen” query niet als tabel getoond wordt in Power BI. Dit doe je door de “Enable Load” functie uit te zetten.

Stap 7

• Nu ga je de gemaakte functie uitvoeren voor iedere rij van de “Koersen” query. Ga naar de “Koersen” query en kies voor Add Column -> Invoke Custom Function.

• Hierdoor geef je 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”.

• Je kan nu de nieuwe kolom openklappen en de Query verder samenstellen. Vervang de punten in de getallen met komma’s om een juist decimaal teken te krijgen. Zorg dat alle kolommen correcte datatypes hebben.
• Hierdoor ontstaat een query die koersinformatie ophaalt van alle aandelen in de AEX.

Stap 8

• Je gaat nu een begin- en einddatum instellen. Want 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 URI 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-202 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.
• Je moet dus twee dingen berekenen. De Unix epoch waarde van de huidige datumtijd en de Unix epoch waarde van 1 januari 2015. Deze zijn te verkrijgen zijn via ietwat complexe M functies.

Einddatum

• Ik kan zowel de begin als 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.
• De M taal is een zogenaamde ‘strongly typed’ taal, je moet altijd het juiste datatype instellen. Je kan twee verschillende datatypes dus niet zomaar van elkaar aftrekken. Ik moet dus 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 je 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.

• Dit 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). Dit wordt echter opgelost als je de data via de Power BI service publiceren. Daar wordt namelijk altijd de UTC tijd gebruikt.

 

Begindatum

• Voor de begindatum maak je eerst een parameter aan. Zodat deze later makkelijker aan te passen is.

• Je kan nu de query van de einddatum kopiëren en de DateTimeZone.FixedUtcNow functie vervangen door de parameter.

• Nu is het 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.

 

Resultaat

• Nu kan je de Query’s uitvoeren en heb je bij iedere verversing de meest recente data. Hierna kan je 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.