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

[BrianJ]

All,

While I think this is a really fun collaborative effort, with a lot of analytically interesting questions that serve as a great way for us to sharpen our Power BI skills, personally I don’t think this is a sound investment strategy. While there aren’t a lot of data points, the performance of funds based on this approach is pretty dismal. Furthermore, if you believe free markets operate according to rational expectations, and particularly as technology has made this sort of analysis available to individual investors, then the forecasts based on technical analysis will already be factored into the market price. I think the chance of this sort of analysis outperforming a low fee, total market index over any significant length of time is nearly nil.

Other certainly may, and probably will, disagree with this assessment, but I just wanted to clarify that my strong enthusiasm for this project is strictly from a learning standpoint and not an endorsement of the strategy itself.

• Brian

[Krays23]

Correct Brian. Intrigues me aswell the possibilities of PBI and I enjoy learning from you guys and seeing all of us come together on a project.

Like I said before its not my full time occupation just merely sharing my experience on what has worked well for me in the past.

I agree with what you said before I don’t have the same skills as you in PBI so better you modify and post pbix updates only here and we just bounce ideas off each other.

Thanks Brian

Dan

[BrianJ]

@krays,

@JarrettM and I have got the period slicer and the dynamic two-year pull working like a champ. I haven’t had time to implement the chart type switcher, but that will be easy and I can do it tonight.

(UPDATE: Chart type switcher is implemented)

At this point, I think we need a clear definition of the specific conditions we’re looking for.

Thanks.

• Brian
  StockList.xlsx (9.4 KB)
  eDNA Forum - Chartist Stock Analysis v2 with Per Table and Dyn Date Query.pbix (875.3 KB)

[Krays23]

Looks fantastic Brian great stuff. Think the best way is to try and start adding the tools to the chart step by step.

The process of analysing charts i like to use is as follows.

Step 1. Main chart behaviour

• is it trending up or down
• oscillating (bouncing up and down)

Step 2. Trend lines

• current major trend lines
• previous major trend lines
• current minor trend lines

Step 3. Support and resistance lines

• channelling lines
• long term resistance lines
• short term resistance lines

Step 4. Chart patterns

• reversal patterns there are 10 of these
• continuation patterns there are 6 of these

Step 5. Indicators
-trending indicators, moving averages need simple ones and exponential moving averages. MACD, RSI, CCI, , Volume

• oscillating - Stochastics

There’s alot to take on here id suggest let’s start putting in some basic indicators then build up to the more complex things. I think you done volume already so can we start with moving averages?

Short, medium and longterm exponential 10MA, 20MA,

Long term simple 50MA, 100MA

Followed by

RSI
CCI
MACD
STOCHASTICS

Let me know if this is too much.

Cheers

Dan

[BrianJ]

@Krays23,

Thanks – that’s a good summary of where we ultimately need to go with this. Can I suggest instead that you take the first run at this, getting as far down the list as you can, and then just give a shout if and when you get stuck?

• Brian

[Ericadyson]

@BrianJ

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.

But how can we make this dynamic… so that I could get yesterday’s share price or last weeks without having to change the date range manually and then pasting the url into the function QuotePull?

[BrianJ]

@Ericadyson,

Good to hear from you – hope you’re doing well.

There at least are a couple of ways you could do this. You could parameterize the timeframe and then pull that into the API on refresh, but the much simpler way to do it unless you’re pulling a huge number of stocks at the same time is to pull the maximum time period by default (2 years in this case), and then filter down to the data you want using a period slicer, as @JarrettM and I did below and in post #42 above. In that post we also included the PBIX file if you want to see exactly how Jarrett added the period slicer to the earlier model.

I hope this is helpful.

• Brian

Power BI Report

[Ericadyson]

@BrianJ

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.

But how can we make this dynamic… so that I could get yesterday’s share price or last weeks without having to change the date range manually and then pasting the url into the function QuotePull? I know you’ll kn

[Ericadyson]

Hi… yes I’m fine and hope you are too. We’re out of lock down in Israel… so that’s a great feeling. Actually, what I needed was just a list of yesterday’s prices. I download every month 3 years worth of stock prices and dividends for 200 odd tickers and that generates the graphs. I use that using an Excel routine by ExcelNet… it’s excellent. That generates the .csv files that I pull in … they go into a query to produce the prices and dividends tables.

But what I wanted, in addition, was a dynamic update of yesterday’s or today’s prices and actually, I am now doing that using Excel’s stock data. I upload that and manipulate a bit via Power Query to convert different currencies to USD and then I both last month’s and the latest prices to do a comparison. It all seems to work quite nicely but you set me on the right course which I NEVER would have done without reading your piece with your marvellous step by step explanations. I’m definitely getting better at all this stuff but still feel so far behind… with so much more to learn (and worrying if my brain is up to it)!!