Multiple Stock Tickers from Yahoo Finance

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
52
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Attachments

  • excel.jpg
    excel.jpg
    73.8 KB · Views: 56

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 11, 2019
Messages
52
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
52
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

DaleKeel

Board Regular
Joined
Sep 11, 2019
Messages
52
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Sep 11, 2019
Messages
52
Office Version
  1. 2013
Platform
  1. Windows
I guess my question is too difficult to answer without upgrading or paying for services. Thanks for the suggestions.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,721
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top