Yahoo Finance API setup in Power BI (Stocks & Shares)

Thats amazing Brian thanks

How do i pull a list of stock symbols rather than individual ones? say i have a list of ones i want and i want to pull them all in one report?

Or even if i wanted to pull all the stocks and shares then filter in PBI … guess the file would be huge and pointless as 90% wouldnt be interested in anyway.

Seems to be alot of people asking that question on the post but im not sure anyone solved it,

I like the look of this can you tell me how this works? im imagining a list of stick symbols as a filter table that you can add as a slicer to your visulization that when clicked would trigger the parameter ?

Often this function will be added to a list of pre-defined symbols. If you had a list of stock symbols you are interested in, you can use the Add Column > Invoke Custom Function. Choose the column of symbols This will repeat the function for each symbol in your list. In this case the symbol will be included already.
Otherwise, you could add a custom column to your function that references the parameter – Add Column > custom column, the code is “=StockSymbol”

Batch queries (large lists of symbols) and optimization will be covered in another blog, it was too much to cover here.

Dan

@Krays23,

I’m very close to having this working. Created an Excel file to hold the list of stock quotes I want to pull. Brought that into the data model, and then added the following function at the top of the M code:

Then invoked this custom function over the StockSymbol table so that the prior code iterates over the StockSymbol table:

Works like a champ in PQ - pulls 5 years of daily quotes for every company in the Excel list, but then when I hit Close and Apply , I get an error. Will need to work on debugging this later, but will get back to you on this.

  • Brian

@Krays23,

OK, it’s working fine now. Culprit was the Power BI supervillain, the always idiotic “Autodetect Relationships”.

Just add the stock symbols of the companies you want to pull to the attached Excel file, save, go to PBI, hit refresh, and you should be good to go:

Revised solution file attached below.

StockSymbol.xlsx (8.5 KB)

3 Likes

Truly amazing work Brian thank you so much mate! your a legend

@Krays23,

Thanks - glad to hear that worked well for you. This was a fun one.

  • Brian
1 Like

@Krays23
Also see this previous post.

https://forum.enterprisedna.co/t/collecting-data-online/10414/4

Rgds,
Alexandre

1 Like

@Alex7891,

The approach in that video is great for pulling single values, but it creates a cached temporary file without a predictable repeating pattern, which is what you need to build a web scraper that can iterate over a table of values. However, it got me thinking about a way to dramatically simplify the code above.

That ended up working so well that I’m doing a video on it today, that should post sometime next week. I think you and @Krays23 will find it interesting. Thanks for the nudge in a new and better direction.

  • Brian
1 Like

@BrianJ Can you have a look see what other values you can pull aswell mate as i need the Volumes as well.

Key values are

  1. Days closing prices (Which we have)
  2. Days volume of shares sold

Thanks Brian your a star

@Krays23,

I actually redid the entire analysis today for the video. In it, we easily pull the following:

Opening price
Daily high
Daily low
Closing Price
Adjusted Closing Price
Total Volume

I don’t have access to my laptop at the moment, but first thing in the morning I’ll send you the revised solution, which pulls all this info and then visualizes it using the OK Viz custom candlestick visual to show open, close, high and low for each datapoint.

  • Brian

Hehe your always a step ahead Brian …fantastic cant wait when I’m a millionaire from trading ill be sure to give you a cut lol

@Krays23,

Index funds, man, index funds…:money_mouth_face:

  • Brian
1 Like

LOL,

Would you be interested in working with me to try and get PBI to recognize certain reversal patterns in the market ?

Dan

@Krays23,

Sure. If we’ve already got the candlesticks, we”re off to a good start. And if we’re going to be doing stuff like Fibonacci retracement, will give me a chance to play with recursive Power Query functions, which I’ve wanted to learn since @Melissa’s Data Challenge #5 entry.

  • Brian

Perfect let me dig out some data that has proven a double bottom reversal pattern to achieve the Fibonacci rule.

What we need is for Power Bi to recognize the Double bottom reversal pattern possibility before it happens, so we can buy at the right time before the Fibonacci kicks in where we make the money.

In technical analysis, a Fibonacci retracement is created by taking two extreme points usually a peak and a trough on a stock chart and dividing the vertical distance by the key Fibonacci ratios of 23.6%, 38.2%, 50%, 61.8%, and 100%.

*Once these levels are identified, horizontal lines are drawn and used to identify possible support and resistance levels on the chart

You up for the challenge?

Sounds interesting
Here are some links to get you started :wink:.

1 Like

HI @BrianJ,

You can get stock symbols from the following website with current price and company name using API. It is very easy to integrate with Power BI. May be first 250 requests are free if you register. I have used it previously and I was available to get a lot of information which was required very quickly.

Kind Regards,
Hafiz

2 Likes

@hafizsultan,

Thanks! In the video I did yesterday, the stock quote “app” gets the list of stocks to iterate over from an Excel file. However, to take that to the next level, it would be cool to embed a PowerApp into that report, where you could enter the list of companies and have the app do the symbol lookup via this API.

I’m just starting to learn PowerApps, so this might be a cool learning project and a good follow on video. Just not enough hours in the day…

  • Brian
2 Likes

@Melissa,

Thanks! I have the Raviv book but haven’t worked my way nearly up to Ch 9 yet. I think your “V” pattern recognition approach from Challenge 5 will be very useful here.

@Krays23 - I’m assuming we’d work through this publicly on the forum where others could learn and/or participate. Just wanted to make sure that was your expectation as well.

  • Brian

Sure why not ! were here to help each other right?

I know the main patterns to look for that are the most powerful share pattern indicators. So then it becomes all about if we can get Power BI to recognize those patterns and highlight to us stocks that are showing signs of these patterns.

I would suggest we start with one pattern like the double bottom and the Fibonacci rule then get that working correctly and go from there.

When can you share the PBIX you have so far that gives us the Shares we want to look at with the criteria like volumes, highs and lows, closing and opening prices?

Cheers

Dan

@Krays23,

Here you go. I did the video at weekly granularity rather than daily, but here’s how to change it back to daily:

and here’s the visual:

The video hopefully will make this crystal clear. It has to go to the editor first, but I expect it will probably post late this week.

If we wanted to get fancy, we could also parameterize the granularity and the time period, so we wouldn’t have to go back to the Yahoo Finance site to do this, but I wanted to keep the video to a reasonable length. To do it, we’d just follow the exact same process used to parameterize the stock quote.

Hope you find this helpful.

P,S, Here’s @Melissa’s awesome writeup of Challenge #5 entry explaining her pattern recognition algorithm:

and here’s her post with the recursive function she wrote but didn’t use for that entry (I think the Little League mercy rule got invoked, and the game got called when she was already up by 15 runs after three innings :grin:):