Pulling Historic Stock Market Information

[Anonymous]

Hi,

I would like to be able to query through a list of stock symbols and get the historic stock prices (plus the symbol in an additional column). For one single stock it works, but I am at a loss how I could make it go through the rest.

Please find here https://gofile.io/d/Xs1mGd the PBIX, where I tried to play with the parameters and invoke function. Maybe somebody would know how to make it work, I'd appreciate it. Probably only the last 5% are missing.

Many thanks!

[mahoneypat]

You can get data for multiple stocks if you first create a function from your Yahoo query with code like that below:

(symboltoget)=>
let
    Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/" & symboltoget & "?period1=" & "1119916800" & "&period2=" & "1593302400" & "&interval=1d&events=history"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
    #"Changed Type"

I called the above function fxYahoo.  You can then start with a Table of stock symols and on the Add Column choose Invoke Custom Function, select fxYahoo and your stock symbol column as the input.  Here is an example query that pulls data for 3 stock symbols as an example.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  This retains the Stock Symbol column, so you can use it in your analysis/visuals.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvR1UYrVAdIhIWAaSMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StockSymbol = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StockSymbol", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fxYahoo", each fxYahoo([StockSymbol])),
    #"Expanded fxYahoo" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxYahoo", {"Date", "Open", "High", "Low", "Close", "Adj Close", "Volume"}, {"Date", "Open", "High", "Low", "Close", "Adj Close", "Volume"})
in
    #"Expanded fxYahoo"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Dear @mahoneypat ,

That looks very promising, but I need a bit more hand holding, apologies. I am a bit lost how and where to enter your code. If I create the function and go to "Advanced Editor" I see the following present:

let
    Source = (#"Stock Symbol" as any) => let
        Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/" & #"Stock Symbol" & "?period1=" & "1119916800" & "&period2=" & "1593302400" & "&interval=1d&events=history"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
    in
        #"Changed Type"
in
    Source

Do I need to replace this with your code or do I need to add it there?

Then for your second code you are writing that this example pulls the data for 3 stocks. Where do I see the limit to 3 stocks? Shouldn't it take just all symbols from the "NASDAQ" or "NYSE" reference?

I don't want to trouble you too much here, but I feel I am so close to a solution and hence would appreciate if you could help me a bit further. If it is faster for you, I would be also happy with a PBIX so I could check how you have done it instead of typing it up.

Many thanks!

[mahoneypat · Accepted solution]

Your Nasdaq and NYSE queries pointed to a local file on your computer, so I couldn't add the function on those tables myself (so I made a dummy table with three symbols).  In your NYSE and Nasdaq queries, on the Add Column tab, choose Invoke Custom function, choose the function (I think our two functions are basically the same), and choose the name of the column with the stock symbol as the input.  You will then see a column of Tables with the returned data for each symbol.  Just expand that column to combine all the tables.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Dear @mahoneypat ,

Thank you very much. It works now perfectly!

[aj1973]

Hi @Anonymous 

I think the problem is the Model, there is no relationship between yuor tables and the source

Look for the symbols in the yahoo.finance add them to the table Yahoo finance, then connect the column to your Tables of the One drive.

Here is an example that is exactly as your Report but i am using Countries instead of stocks

https://app.powerbi.com/view?r=eyJrIjoiMjc2N2ZkZmYtMTNjYi00N2Q0LWE4MmUtYjhiY2E4NTQxZGM1IiwidCI6IjgzNTU5ODFiLTJlYTYtNDdjZi04ZjJiLTc3MTY3N2FmZjMyZCJ9

Regards   

[Anonymous]

Hi @aj1973 ,

Many thanks for your time and response.

I am not quite sure whether it is indeed the relationship between the tables as the table "NASDAQ" and "NYSE" are only listing the symbols I would like to use as parameters. If you look at the source of "Yahoo finance" you can see that I am using the parameter "{Stock Symbol}" as a placeholder for the stock symbol. For the "current value" set in the parameter it works fine, but I would like that it now goes through the rest of the "NASDAQ" resp. the "NYSE" entries. The stock symbol itself can't be retrieved through the below query....it needs to be "parsed".

https://query1.finance.yahoo.com/v7/finance/download/{Stock Symbol}?period1=1119916800&period2=1593302400&interval=1d&events=history

See the source setting for "Yahoo finance" here: https://gofile.io/d/C5RlcZ

Maybe I am wrong and I have missed your point?

Many thanks!

[aj1973]

@Anonymous 

The relationship between your tables are defenitly needed if you want to filter through the symbols. 

Besides take a look at your 'Yahoo finance' table and tell me if you can tell each row belongs to what symbol or stock!

I am sure there is a column for the stocks in the source however i am not able to open  it  https://query1.finance.yahoo.com/v7/finance/download/%7BStock

the source should look like this https://ca.finance.yahoo.com/most-active

Hook me up with a good source where i can see the symbols in order to help you

Regards  

[Anonymous]

Yes, I can as I invoke the symbol if you check the last step on that table. I think the problem really is that the URL lookup result gives you just the numbers without the symbol. Hence, I was hoping there is a way to parse the list of symbols as parameter and secondly to add the parameter on each of the processed rows. For a single stock everything works as expected.

Not sure I make sense to you, but I have no other source as this is exactly the challenge here...

Many thanks for your time!

[aj1973]

Gotcha,

I think the URL lookup is returning one value that's why you get only one stock.

Anyway here is what get when try to connect to your source

Sorry, i tried my best.

Good luck

[Anonymous]

Try this 

Anyway, thank you for trying to help me, much appreciated!