Multiple Stock Tickers from Yahoo Finance

[DaleKeel]

I saw the following request:
How do I set up a web query in excel and make it dynamic for yahoo finance? I’d like to have a reference cell for the ticker symbol of which the web query references this cell. Therefore when the query is refeshed it reflects the stock ticker in the reference cell.

And I saw the response below:
--------------------------------------------------------------------------------------------------------------
Here's one way:

• Put a stock symbol in a cell and name that cell RefSymbol
(I put BAC in that cell)

• Create a blank query in Power Query
• Edit the query in the Advanced Editor and replace ALL of the M-Code with this:


let
Symbol = Excel.CurrentWorkbook(){[Name="RefSymbol"]}[Content]{0}[Column1],
BaseURL = "https://finance.yahoo.com/quote/SYMBOL?p=SYMBOL",
URL = Text.Replace(BaseURL,"SYMBOL",Symbol),

Source = Web.Page(Web.Contents(URL)),
#"Filtered Rows" = Table.SelectRows(Source, each ([ClassName] = "W(100%)")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Symbol", each Symbol),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"Column1", "Column2"}, {"Column1", "Column2"}),
Result = if Table.IsEmpty(#"Expanded Data") = false
then #"Expanded Data"
else null
in
Result

---------------------------------------------------------------------------------------------------
As of this time, that query returned:
Code:
Column1 Column2 Symbol
Previous Close 30.63 BAC
Open 29.55 BAC
Bid 28.45 x 1800 BAC
Ask 28.74 x 900 BAC
Day's Range 28.91 - 30.30 BAC
52 Week Range 26.21 - 35.72 BAC
Volume 117,103,804 BAC
Avg. Volume 46,852,875 BAC
Is that something you can work with?


-----------------------------------------------------------------------------------
I got this to work with one ticker symbol and one query. But dealing with 15 or more stocks and 15 or more queries is impossible for me. I have no idea how to get this to work with many symbols with one query. Or how to add additional symbols without more queries. Could that be done with many symbols in one query and add symbols as needed? Would it be possible to have the reference cells down Column A and have the Data in rows? This would make analysis much easier and clearer. I have upload an example of how I think it should look like. I have home an student 2013 version of Excel.

Thanks Much for your Brain Energy and your Time.

 

[DRSteele]

You've cross-posted with this Post, and doing so is considered naughty.

Why use the Query method just to get a current quote? I have many workbooks full of stock quotes that utilise the new Stock Data Types available in Excel 365. See this video and others about Stock Data Types in that channel to see if it suits your needs. Remember that it will not download historical data, though the rumour is that MS is working on it.

 

[DaleKeel]

I found the request last Thursday. I made a reply asking for help. No one replied. I was thinking that my reply only went to the requester that needed help and could not help me so that is why I made a new thread. I am still very new to this forum and the proper ways of asking for help.
I have excel 2013 so I can not use the newest features of Excel 365. Sorry.

I use all of the columns in my example except for col H and I. By having them in this order and the possibility of adding more symbols to the same query makes it much easier to extract the data and put them on another sheet to my make my trading decisions.

Thanks for your interest in helping me solve my problem.

 

[DRSteele]

One of the problems I have encountered over the years is that once you get a query set up so that it works okay, there seems to be a limited period when it works at all. Usually what happens is the website changes the way its tables can be referenced by Excel and then your whole project fails. Or it could even be that Microsoft changes something and it all goes pear-shaped. I have dozens of iterations of files I was using to get quotes from google.finance.ca using Microsoft's native api external Existing Connections, only to find one day that it all just stopped working.

Another thing you can do is pay a little bit and get access to data from an aggregator (like Bloomberg) or markets themselves. For instance, the TMX has powerstream that links to Excel, and historical data is available.

You could also pay a little bit for 365 and you'll have all the amazing new features, including Stock Data Type.

Well, best of luck. Buy low, sell high.

 

[DaleKeel]

I really to not want to invest in new software or services. Sorry. I have just started trading stocks because of the current opportunities in profits. I have traded Cryptocurrency since the beginning of 2017. My trading will probably not go on for years, I am 72 years old. I have dealt with APIs for awhile and I saw where Yahoo changed in 2017. I know things change but for them to make changes to their table structures cost them a lot of money and they would not do that unless it made them more money from the changes. Thanks for your continued input.

 

[alansidman]

You may wish to look at this site and see if this add-in will do what you require.

Stock Connector add-in for Excel

www.michael-saunders.com

 

[DaleKeel]

Thanks for your input. I saw this ad before. Really not interested in having to have something that someone else is in control of the data, the layout, and the possibility of getting in a "plan" that can increase in costs over time and me being stuck because I subscribed. Reminds me of cable TV

 

[DaleKeel]

I guess my question is too difficult to answer without upgrading or paying for services. Thanks for the suggestions.