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

Power Query Data Setup
1

HI,

Can anyone help me with how to set up this API in Power BI to retrieve the stocks and shares from Yahoo finance?

Not sure how i put this into the advance editor exactly or can i do it by connecting by selecting web? bit confused.

This seems to be the URL = yahoofinance-stocks1.p.rapidapi.com
This is the API = fba25b5897msh988ae2b4516fe0cp1a4802jsnb596eb1ff603

var unirest = require("unirest");

var req = unirest("GET", "https://yahoofinance-stocks1.p.rapidapi.com/dividends");

req.query({
	"Symbol": "MSFT"
});

req.headers({
	"x-rapidapi-host": "yahoofinance-stocks1.p.rapidapi.com",
	"x-rapidapi-key": "fba25b5897msh988ae2b4516fe0cp1a4802jsnb596eb1ff603",
	"useQueryString": true
});


req.end(function (res) {
	if (res.error) throw new Error(res.error);

	console.log(res.body);
});

Thanks guys

2

@Krays23,

See if this works for you:

image
image1002Ă—571 26.1 KB 

let
    Source = Json.Document(Web.Contents("https://query1.finance.yahoo.com/v8/finance/chart/"

,[RelativePath = #"Stock Symbol"  &"?range=5y&interval=1d"]

)),
    chart = Source[chart],
    result = chart[result],
    result1 = result{0},
    Branch = result1,
    timestamp = Branch[timestamp],
    #"Converted to Table" = Table.FromList(timestamp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each 25569 + ( [Column1]/60/60/24 )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    EndBranchDate = Table.Buffer(Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}) ),
    Custom1 = Branch,
    indicators = Branch[indicators],
    adjclose = indicators[adjclose],
    adjclose1 = adjclose{0},
    adjclose2 = adjclose1[adjclose],
    #"Converted to Table1" = Table.FromList(adjclose2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    EndBranchPrice = Table.Buffer(Table.AddIndexColumn(#"Converted to Table1", "Index", 0, 1) ),
    #"Merged Queries" = Table.NestedJoin(EndBranchPrice, {"Index"}, EndBranchDate, {"Index"}, "EndBranchPrice", JoinKind.Inner),
    #"Expanded EndBranchPrice" = Table.ExpandTableColumn(#"Merged Queries", "EndBranchPrice", {"Date"}, {"Date"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded EndBranchPrice",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Price"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price", type number}})
in
    #"Changed Type"let
    Source = Json.Document(Web.Contents("https://query1.finance.yahoo.com/v8/finance/chart/"

,[RelativePath = #"Stock Symbol"  &"?range=5y&interval=1d"]

)),
    chart = Source[chart],
    result = chart[result],
    result1 = result{0},
    Branch = result1,
    timestamp = Branch[timestamp],
    #"Converted to Table" = Table.FromList(timestamp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each 25569 + ( [Column1]/60/60/24 )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    EndBranchDate = Table.Buffer(Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}) ),
    Custom1 = Branch,
    indicators = Branch[indicators],
    adjclose = indicators[adjclose],
    adjclose1 = adjclose{0},
    adjclose2 = adjclose1[adjclose],
    #"Converted to Table1" = Table.FromList(adjclose2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    EndBranchPrice = Table.Buffer(Table.AddIndexColumn(#"Converted to Table1", "Index", 0, 1) ),
    #"Merged Queries" = Table.NestedJoin(EndBranchPrice, {"Index"}, EndBranchDate, {"Index"}, "EndBranchPrice", JoinKind.Inner),
    #"Expanded EndBranchPrice" = Table.ExpandTableColumn(#"Merged Queries", "EndBranchPrice", {"Date"}, {"Date"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded EndBranchPrice",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Price"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price", type number}})
in
    #"Changed Type"

Solution based on this article:

https://powerbi.tips/2019/10/historical-stock-price-function-in-power-query/

Full solution file attached below. I hope this is helpful.

1 Like
3

HI Brian thanks for this.

I cant ipen your file at work as its an older version of PBI. Ill have to try at home later.

I tried to copy your M code into advanced editor and get an error.

image
image1143Ă—1001 257 KB

I want to be able to see all stocks and shares data is that possible with what you provided or jus Microsoft?

Thanks

Dan

4

@Krays23,

Sorry – my bad. It looks like I accidentally pasted the code in twice. Here it is again, this time just pasted once…

let
    Source = Json.Document(Web.Contents("https://query1.finance.yahoo.com/v8/finance/chart/"

,[RelativePath = #"Stock Symbol"  &"?range=5y&interval=1d"]

)),
    chart = Source[chart],
    result = chart[result],
    result1 = result{0},
    Branch = result1,
    timestamp = Branch[timestamp],
    #"Converted to Table" = Table.FromList(timestamp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each 25569 + ( [Column1]/60/60/24 )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    EndBranchDate = Table.Buffer(Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}) ),
    Custom1 = Branch,
    indicators = Branch[indicators],
    adjclose = indicators[adjclose],
    adjclose1 = adjclose{0},
    adjclose2 = adjclose1[adjclose],
    #"Converted to Table1" = Table.FromList(adjclose2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    EndBranchPrice = Table.Buffer(Table.AddIndexColumn(#"Converted to Table1", "Index", 0, 1) ),
    #"Merged Queries" = Table.NestedJoin(EndBranchPrice, {"Index"}, EndBranchDate, {"Index"}, "EndBranchPrice", JoinKind.Inner),
    #"Expanded EndBranchPrice" = Table.ExpandTableColumn(#"Merged Queries", "EndBranchPrice", {"Date"}, {"Date"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded EndBranchPrice",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Price"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price", type number}})
in
    #"Changed Type"

In the PBIX file, I also set up a parameter query to define the Stock Symbol parameter in the above code:

image
image899Ă—974 17.4 KB
.

I added AAPL and AMZN, but you can modify the picklist to whatever you want.

  • Brian
1 Like
5

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

6

@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:

image
image1014Ă—200 10.4 KB

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

image
image1056Ă—466 12.1 KB

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
7

@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:

image
image1013Ă—567 34.3 KB

Revised solution file attached below.

StockSymbol.xlsx (8.5 KB)

3 Likes
8

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

9

@Krays23,

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

  • Brian
1 Like
10

@Krays23
Also see this previous post.

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

Rgds,
Alexandre

1 Like
11

@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
12

@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

13

@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
14

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

15

@Krays23,

Index funds, man, index funds…:money_mouth_face:

  • Brian
1 Like
16

LOL,

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

Dan

17

@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
18

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?

19

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

1 Like
20

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