Are you interested in finance? Investing? Stock Market? Just want some finance data to play with? Want to build you fancy investments tracker? Do you want the latest stock market data and you don't know where to get it?
Well... I might have a solution for you! Check this out...
If you're into finance and stocks, you probably heard about Yahoo Finance. It has all the data of stock markets. The question is... how do you get it?
It's not as complicated as you probably think it is...
1) Go to the company you want to get the stock market data from.
In my example I will use Netflix: https://uk.finance.yahoo.com/quote/NFLX?p=NFLX
2) You can see already that there is a way to export the stocks data in the Historical Data tab.
After jumping to the Historical Data tab we need to make a few selections...
First you need to select the Time Period you want. I selected the max time period to get all the stocks available from Netflix. I kept the default options for the data to Show (Historical Prices) and for the Frequency (Daily)
After the selections are done your URL is ready to be used in Power BI! You just need to right click on the Download Button and then Copy link address:
3) You will now connect to Yahoo Finance data in Power BI. To do this open Power BI Desktop and select Get Data from Web.
https://query1.finance.yahoo.com/v7/finance/download/NFLX?period1=1022112000&period2=1589241600&interval=1d&events=history4) You should now see a window like this one:
All your stock market data is now in Power BI. Click on Transform Data.
There is a problem to solve though... How are we going to update this data? I'm sure you don't want to copy paste the URL every time you want to see the latest stock market values...
Let's have a look at the URL:
https://query1.finance.yahoo.com/v7/finance/download/NFLX?period1=1022112000&period2=1589241600&interval=1d&events=historyThere are a few things to notice:
- The stock code: NFLX
We can use this code to easily access more companies/index/etc
- The time period: period1=1022112000&period2=1589241600
You can define automatically the start and end period of the stocks you want to retrieve. My suggestions is: stick to a time period that is equal to all stocks. Instead of choosing max, choose for example the last 10/20 years.
The time stamp is in epoch format. We will talk more about this in the next step.
5) The epoch time... Why is it important?
If you want your report always up to date, you will need to get the current epoch time to replace in the period2 part of the URL, every time you hit refresh. I'm sure there are a few weird calculations you can do, but I prefer to make use of our good old friend: Google!!
So what I did was pretty simple, just googled Epoch Time and I came up with this web site:
Someone will get the current epoch time for you, every time you refresh.
Oh I love the internet!
6) You just need to use the web connector again, just like we did for the Yahoo Finance page.
However, Power BI will return something very strange:
Well... again, let's opt for the easy choice and click on Add Table Using Examples.
7) Here you just need to start writing the epoch time that is showed and Power BI will automatically recognise the field you're trying to use. From now on this will be updated automatically. I named this table EpochTime.
8) There is just one table missing...
Like I mentioned before you might (and probably will) want to follow more companies. In that case all you need to do is add a new table. It can be in excel, SharePoint... I did it as a Power BI table because, again, I'm lazy. I called this table Company:
If you're wondering where did I get the codes for each company/index, it's in the Yahoo Finance Company site, along with the name:
9) Next we will have to build 2 parameters: one for the company code Comp and another one for the last epoch date LastDate. This parameters can have any value. In the Query Editor click on Manage Parameters:
Add the two new parameters and click OK.
10) For our query to dynamically read our parameters, we need to transform the first query we did (get the data for Netflix) into a function:
let
Source = (LastDate as text, Comp as text) => let
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&Comp&"?period1=1022112000&period2="&LastDate&"&interval=1d&events=history"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
#"Changed Type"
in
SourceThe modifications we did yo our query are represented in green and called our function NFLX.
The initial web URL we used will no longer be static, instead it will dynamically change to the companies we add and to the current epoch time.
10) Back to our Company Table, we will now get everything together.
First, we will add the epoch time. To do this click on Add Custom Column:
If you notice we are retrieving the entire Epoch Time Table. Good thing for us it only has one value on it: the last current epoch date!
After adding the new column click on Expand to New Rows:
11) Almost done! Remember that query we converted into a function earlier? You are gonna need that now!
Let's do all the magic with Invoke Custom Function! In the Add Column Tab click on Invoke Custom Function:
Now we just need to choose which function we want to call (in this case I called my function NFLX). Because this function had two parameters on it we need to choose what column values do we want to use in the parameter (in this case we want to use the Comp and LastDate). After mapping the parameters with the correct columns click OK:
12) Finally, the last step!
As a result of the previous step you will have a table containing all the information on the list of companies, index, cryptocurrencies you currently have.
To finalise your query you just need to click on the Expand Button, select the columns you want and that's it! Whenever you hit refresh you will always the latest stock market data!
And txaram! Happy stock market reporting everyone!
You can get the .pbix template file of this tutorial in my GitHub account:
Interested in learning more about Power BI Report Design? Then the Power BI Report Design Bootcamp is for you!