Hi,
I am trying to create a stock screener through excel. First off, Excel may not be the best choice for stock screen creation, so I'm open to alternatives, but 1. I'm almost done, and 2. I have zero programming experience
So before I tell you my problem, here's what I've got so far (Working on the ROIC tab):https://jumpshare.com/v/OxlCrsMZa18tS9bB8N1S?b=D89mk1t3hPnWdxyYxL6p
What I can't do, and what would complete the screener, is make a query for every single cell. Now I think, although I don't KNOW, that it is possible to create a macro which starts a query, tells excel where to put the data (50 rows down from the previous input), and tells excel where to find what ticker to put in (one row down from previous ticker). The query making also has to repeat to the end of the tickers. (you may be thinking: "isn't the excel row limit approx. 1 million?" It is, and my solution to that is to put half the balance sheets from columns A:F, and the other half from H:M. You don't need to include that, though, I can figure it out.)
The query pulls company balance sheets from yahoo finance, and then imports the data to the Data sheet. Since the balance sheets are all formatted the same, it is easy to sort: each metric is 50 rows down from the previous search.
The reason I need to automate this query creation process is that there are approximately 25,000 stocks, and to make a screener, I need to analyze them all at the same time; each stock, therefore, needs its own query and own data.
I've tried recording a macro, but I just can't seem to get it to repeat, offset 50 rows every new data query, and offset 1 row every new ticker.
I would really appreciate the help; I am very new to excel, and I'm not entirely sure how possible it is to do what I'm asking. Thanks!
I am trying to create a stock screener through excel. First off, Excel may not be the best choice for stock screen creation, so I'm open to alternatives, but 1. I'm almost done, and 2. I have zero programming experience
So before I tell you my problem, here's what I've got so far (Working on the ROIC tab):https://jumpshare.com/v/OxlCrsMZa18tS9bB8N1S?b=D89mk1t3hPnWdxyYxL6p
What I can't do, and what would complete the screener, is make a query for every single cell. Now I think, although I don't KNOW, that it is possible to create a macro which starts a query, tells excel where to put the data (50 rows down from the previous input), and tells excel where to find what ticker to put in (one row down from previous ticker). The query making also has to repeat to the end of the tickers. (you may be thinking: "isn't the excel row limit approx. 1 million?" It is, and my solution to that is to put half the balance sheets from columns A:F, and the other half from H:M. You don't need to include that, though, I can figure it out.)
The query pulls company balance sheets from yahoo finance, and then imports the data to the Data sheet. Since the balance sheets are all formatted the same, it is easy to sort: each metric is 50 rows down from the previous search.
The reason I need to automate this query creation process is that there are approximately 25,000 stocks, and to make a screener, I need to analyze them all at the same time; each stock, therefore, needs its own query and own data.
I've tried recording a macro, but I just can't seem to get it to repeat, offset 50 rows every new data query, and offset 1 row every new ticker.
I would really appreciate the help; I am very new to excel, and I'm not entirely sure how possible it is to do what I'm asking. Thanks!
