Repeated Web Query to fill a column with data

[Nerpilis]

I'll begin by mentioning that I'm relatively new to VBA and Macros, (currently learning R) but have enough intuition to figure things out once I'm pointed in the right direction. There is probably a more elegant way of achieving what my sample spreadsheet is trying to do; feel free to redirect my efforts.

I have simplified this spreadsheet from the original to get to the core of what I want it to do. There are three tabs: 1) tab:"query fields" is an area that places data that is retrieved from the web, in this case it retrieves tables from yahoo finance given a ticker input. The ticker input cell is currently on the "score card" tab. 2) tab:"score card" in this tab you can input a ticker symbol in the indicated box and it will populate the "query fields" with corresponding data. This tab also extracts certain fields from the retrieved tables and performs a pass/fail test then produces a score %

This is great if I'm doing one symbol at a time, however I eventually would like to 'loop' this process such that a score field is populated for a list of tickers, hence tab 3) "list". Ideally I'd create a control button to initiate the web retrieval process and it would go down my list, retrieve the table data, perform the pass/fail tests, then populate the corresponding score % in the cell next to the ticker symbols. I know I can keep running separate web queries for specific symbols, but I guess my secondary goal is to have this be as dynamic as possible, since the ticker list will change and the list could be sizable at times.

Any help or direction is appreciated. I'm also not married to Yahoo Finance for retrieving stock data, it's just convenient, if other web sources yield similar or better results feel free to share.
CSH- web query.xlsx

[AlphaFrog]

There are several existing templates and add-ins that can do this already. Have you tried any of them? Do a web search for something like Excel get yahoo stock quotes.

Here's one that seems to be similar to what you describe.

Multiple Stock Quote Downloader for Excel

[Nerpilis]

Thank you for the link. I have used similar templates in the past, some have varying degrees of accuracy and variations of retrievable fields. This one seems to be using similar code to the ones I’ve experimented with, with the added value of csv export toggle. In addition the explanation provided in the link, it tells you how you can adjust the template to have the data side by side for each security open/close value – It definitely gives me an idea for a future project. Unfortunately it’s not getting to the data points/tables that I’m interested in, it’s retrieving historical price information but I’m unclear how I would adjust the code to get to other metrics offered in Yahoo Finance…. Specifically tables from the Key Statistics page & Summary page. If I can get that down, eventually I’ll learn how to import data from tables in Financials and Option strings, but I need to learn to crawl before jogging.

I suppose I’m looking for more understanding of what’s going on behind the scenes, perhaps I need to re-phrase my Q with something more code specific. The problem is my syntax is terrible & I’ve never been formally trained in a programming language. I know I need some sort of For/If Loop such that the retrieval process is repeated for each ticker, I don’t necessarily need the output in separate tabs but I can understand if that’s the only way. If each retrieval process has to go into another tab, how would I set up my ‘display/calculation’ tab so that it’s retrieving data field records from an ever changing name/source tab?
I’ll continue to practice/search for a loop learning site or a template I can figure out to how to manipulate for my purposes.

[AlphaFrog]

I had suggested looking at the other templates because I didn't know if you were simply trying to reinvent the wheel. Apparently you have specific requirements the other templates don't quite fill.

Give this a try with your example workbook. Format the ticker list score column as percent (or however you prefer).

Please Login or Register  to view this content.