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.
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:
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.
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.
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.
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.
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 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.
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…
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.
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?
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.
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 ):