Stock Market Data in Power BI: Yahoo Finance’s Hidden Hack! #Finance

let’s start with yaho Finance so previously you were able to go to the website open some stock and today we’ll be working with Apple stock I’m a fan what can I say

go to historical data and here you have link to download uh historical data in CSV format you could easily put it into pobi and work with that however you please however as of recently yaho none;">Finance removed that option but there is a workr if you go back to summary if you start inspecting the page and you go to network Tab and let’s refresh it just so that we can see all of the requests that page is making you can actually intercept one API call the internal API call

which this chart on the left makes if you look at it there is some structure right if you put if you copy paste it and put it into Postman and you know I’m a fan of Postman and click send you will actually get response from that apis there’s no authentication nothing and you get all of

the data that chart on the page receives so there is symbol exchange name some basic information about the stock uh some movements within the day and if you scroll down let’s scroll down a lot of scrolling you can actually get daily uh historical data open close high low Etc I will spur you

the details you can look into the Json structure to understand it and let’s switch to pobi and try to extract the data in po pi and work with it so if I switch here this is the end result uh what I will be extracting here is the date column open Prime high price low price close price volume

and adjusted close price although this is optional actually let me switch to Advanced editor and let’s go through the call that will be easier I hope you watch my CashNews.co about relative pass in when you work with uh API calls this function allows you to refresh apis within Pro service so

use it and watch that CashNews.co Let’s let’s have a look back at the URL here we have query 1 although query 2 works as well I guess they have few service that uh servers that serve that API so query to #1a73e8; text-decoration: none;">Finance yahoo.com this is our base URL then in relative pass we have few things we need to specify version 8 Finance chart and our stock ticker

right if we take a look here this is exactly what we have here version 8 Finance chart Apple AAP okay and there are two parameters that we can pass if you look here you can actually see

options that you can pass here valid ranges so there’s data granularity and there are valid ranges one day 5 Days 1 months Etc maximum I don’t want to do maximum right now I’ll shorten it however it’s up to you so interval I’ll specify as one day I want daily data on a

daily level and range I want data for one year okay so those are two parameters that they specify after that what you have do have to do is you have to extract all of the necessary uh Fields columns whatever from Json stru uh Json structure from the response that you’re getting from the

server this is where the magic happens so the important thing is um I would like to store results in a separate uh variable called results okay and from The Source I’m extracting array called chart within that rate I’m extracting a rate called result and I’m only extracting first

and the cting starts from zero so I’m only extracting first result there will be a lot more but I need only the first one meta okay I’m extracting some metadata that API response provides and I will be switching between uh Postman and pobi just to show you what’s inside meta this

is what we have inside meta so I’m extracting this array as well into separate variable that I will be working with and then I’m extracting separate uh values or parameters however you call it from that uh method array currency symbol exchange name instrument name First trate Data time

zone Etc although most of it I will not be using specifically in this example it will still be useful in other uh queries that I have what do we need here we need time stamp this is our date uh so results time stamp uh this one is a important quotes so we’re extracting results indicators

quote and again just the first result and from quotes from that array we’re extracting our volume you can see quotes volume I’m referring to that nested uh column uh close prices so close high low open and adjusted close close is stored separately it’s stored in indicators

adjusted close first adjusted close so you really have to dive into the structure of uh Json response if you want to extract proper data once you’ve done that uh since I don’t need all of the parameters here I’m only selecting those that I want to see here so I’m uh

converting into table selecting The Columns and here I’m specifying which columns do I need it’s time stamp close price volume high price low price open price adjusted close so you can see here in curly brackets I’m specifying columns that I’m extracting from the Json

response and the names that I want to specify for them once you do that though and let me switch to the data so you see for timestamp you will get uh numbers like this those are Unix timestamps which have to be converted into daytime regular daytime format so to do that you will have to add custom

column let me switch here and this how you transform Unix Tim stamp you have to do addition once you do you will get proper date here then you have to specify data types most of it is just uh decimal numbers and here you have the date and since you no longer need a timestamp you can remove it what

I like to do because I also like reordering columns as well as removing columns that that I don’t need I usually use select columns instead of having two steps in my PO query first uh deleting The Columns and then reordering columns I use just one this is Select columns in this case you can

only specify columns that you need but also uh it follows the order that you specified here so you can just save one step so here I’m selecting date open price high low Etc everything except for time step which I no longer need and once you finish that you have historical data for stock quote

prices what you can do after that and I’ve done that here already you can convert that into function and by doing so you can make list of uh stock tickers attach function to that and make table with multiple uh stock historical data so let’s see how does that work what you will need to

add first is name of your function as well as what what you will be passing as an input to this function in our case this is a ticker AAP or msvt Microsoft uh s text Etc then the rest is more or less the same but you are changing and adding ticker here to relative pass this is the the change and

then at the end you have to make sure that you are also closing properly with your function reference which you started with in your closing is okay better and so you will transform it into function and then you can pass some tier here and query uh the data what you can also do is to make the table

in here let’s see let me delete this one a msvt okay we can add Uh custom column invo custom function I will not be changing the name you can specify that function that we just created you can specify that uh the column that will be used as an input is your ticker column where you specify

tickers that you want to get historical data for and once you do uh PBI executes the function queries the data and creates tables for you and after that you can expand those tables and you will have historical data for your stocks that’s how easy it is and that’s all for today subscribe

and see you in the next one