In this blog, I’ll show you how to create a dynamic stock quote app in Power BI. This interesting technique came out from a post in the Enterprise DNA forum.
The member wanted to know how we go about starting with online data and pulling that into Power BI and then analyzing it from there instead of starting with what we typically do, which is an Excel file or a CSB file.
Similarly, along those lines, I’ve got a question from another forum member about pulling information off of the Yahoo finance site. And so, he and I worked through an interesting solution on that.
It’s a dynamic solution where you can pick whatever stocks you want, and it’ll give you that High/Low/Close chart. You can pull in any granularity you want in terms of daily, weekly, monthly, and annually.
And so, I wanted to show you how to do this and how easy it is. It shows how powerful Power BI is in terms of web scraping and automated data poll capabilities.
Instead of doing what I normally do, which is to work through the demonstration in an edited way and kind of hit the highlights, I’ll show you from start to finish. We’re going to build this from scratch.
Pulling Online Data Into Power BI For The Stock Quote App
We’re going to start with an empty file and the only thing I’ve done to it is I’ve added two custom visuals. I’ve added that High/Low/Close candlestick chart and a chiclet slicer, which is one that isn’t necessary, but I like it better than the default slicer. You can see that there’s no data yet and no visuals as well.
I’m putting 15 minutes on the clock and then build and visualize this stock quote app in under 15 minutes. We start at the Yahoo Finance site and let’s just do a Quote Lookup.
It doesn’t really matter what quote we pull and you’ll see why in a minute. So for this example, let’s pull Netflix.
Let’s go down to historical data and we can choose whatever granularity we want. Let’s say we want three months and we want to pull that for the weekly close, just so that it doesn’t overwhelm the High/Low chart. Then, click Apply.
And then instead of clicking download, we right-click and copy link address since we want to pull the online version.
Inside Power BI, we go to Get Data, and we’re going to pull this through a web connector.
We can just use the standard basic web connector. We don’t need to do anything fancy here. We just paste the copied link and hit the enter key or click OK.
You’ll see that this pulls it in really nicely and we don’t have to do anything. We’ve got all we need here.
Transforming Online Data
We then go into Transform Data and into the Advanced Editor. It’s pretty compact for what it’s doing, but the thing we want to look at here is the Netflix symbol (NFLX). This is the static part of it that we don’t want to have to pull that quote for each stock that we want.
So we’re going to turn this into a function. To do that, we’re going to say StockQuote “as text“, and then “as table” since we want a whole array of quotes to pull, and we want to iterate over that table. Finally, we just put the arrow, which turns it into function.
And now, we’ll replace this static part with our parameter. And now, we’ll replace this static part with our parameter, then click Done. This will take that URL, and then each time it iterates, it’s going to pull the stock quote from the table.
We can then change the name of this function. I’ll call it Quote Pull.
Creating A Table Of Stocks
The next thing we need to do is to create the table of stocks over which the function is going to iterate. And we’re going to do that by pulling that in from Excel.
In Excel here, we have a file called StockList and we have a column called Quote Symbol, which matches our parameter name. Now let’s take the basket of stocks that we want to pull and then save the file.
Inside Power BI, we go and pull that file in.
And this is how it looks like.
Now we can transform that. Note that we need to Use First Row as Headers. Let’s then change the name of this file to StockList.
Iterating The Quote Pull Function Over The StockList Table
We want to iterate that Quote Pull function over our StockList table over each of the stocks that we’re watching. So we go to Add Column and click on Invoke Custom Function.
Let’s call this Pull column and the function query is going to be our Quote Pull function. The columns that we’re going to iterate over is our QuoteSymbol.
So now we have a table for each of the stocks we’ve got in our StockList table.
Then, we make some adjustments here. By clicking on the expand button, we go and uncheck the Use original column name as prefix.
Changing Column Type Accordingly
The last thing we have to do is to change the column types and make sure that they are appropriate to their respective columns.
When that is done, we go to the Home tab and click Close & Apply.
Building The Dynamic Stock Quote App Graph
And now our web scraping is done, let’s start doing some graphing. Here we use the candlestick table and just adjust the size to fit into the page, as it is the focal point of the page.
Then, from the Fields, we drag our Date, Open, Close, High, and Low fields into the table. And you can see how they all come together.
Next, we turn the High/Low caps on and then format our Date.
Now all we need to do is throw our chicklet slicer in with our Quote Symbol.
We can go into the General option here and make some more adjustments. Let’s put a bunch of columns in here, say 15 columns if we have a lot of stocks.
We can also adjust the height or width if we want, and then turn the Multiple Selection off and the Forced Selection on.
We could also change the size and color of the chicklet to make it look better. This is the final look of the dynamic stock quote app.
In just a few minutes, we built a full stock quote app and made it dynamic. We’ve graphed it out and it look great.
If you really want to get fancy with this, you could go back to that URL and you could change the granularity parameters. You could also add more slicers if you want, and more.
I hope you enjoy this and find it useful. Learn from this and implement it in your own work environment.
All the best,
***** Related Support Forum Posts *****
Extracting the Web Data from Multiple Pages and Tables
Collecting Data Online
Yahoo Finance API Setup in Power BI (Stocks & Shares)
For more collecting online data support queries to review see here…