-1

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!

pnuts
  • 58,317
  • 11
  • 87
  • 139
Jake C
  • 1
  • 1

1 Answers1

0

I can't help but think you'd be better off using a database (such as Microsoft Access) instead of Excel to store this amount of data. You could still run your calculations from a spreadsheet and query the data table in the Access database. It's a fun (and maddening!) journey to develop your own Excel application. Having said that...

What you need to study in VBA is looping. Start with a simple loop on a set of the ticker values in the column:

Option Explicit

Sub test()
    Dim ticker As String
    Dim companyTicker As Range
    Dim allTickers As Range
    Dim lastRow As Long

    '---sets up a range containing all your ticker strings
    Dim lngMaxRow As Long
    lastRow = Sheets("ROIC").Range("B" & Rows.Count).End(xlUp).Row
    '---during your development, force your ticker set to be shorter
    lastRow = 10
    Set allTickers = Range("B5:B" & lastRow)

    '---now loop through all the companies and get the info for each one
    For Each companyTicker In allTickers
        ticker = companyTicker.Value
        '--- build up the query to Yahoo Finance using the company ticker string
        '    copy the data from your query to the cells in your workbook
        Debug.Print "ticker is " & ticker  'use a debug.print to check as you write the code
    Next companyTicker                     'get the next company ticker
End Sub
PeterT
  • 8,232
  • 1
  • 17
  • 38