You can make Excel scan stocks automatically. In this post I´ll show you how to:

  • Automate Excel to download historical stock prices using a web query
  • Scan historical stock prices to identify trends using latest 50 day average
  • Iterate through each stock ticker in a list using vba

I have created a workbook with two sheets, Overview and Calculation.

You can download the complete workbook at the end of this blog post.

Automate Excel to download historical stock prices using a web query

I am going to use yahoo to download stock prices for each stock ticker. You can find historical stock prices in Yahoo Finance. Here is an example:

http://finance.yahoo.com/q/hp?s=CAT

The web page shows historical stock prices for Caterpillar. Caterpillar´s stock ticker  is CAT. To find stock ticker/quotes type a company name in the search field and click "Get Quotes". Yahoo Finance covers a lot of stock markets all over the world.

Create a web query

  1. Select sheet "Calculation"
  2. Select cell A1
  3. Click tab "Data"
  4. Click "From Web"
  5. Copy http://finance.yahoo.com/q/hp?s=CAT into the address field and click "Go"
  6. Click "Black arrow in yellow box" next to historical prices table

     

  7. Click Import!

Now we have a web query we can use to iterate or loop through all our stock tickers.

Scan historical stock prices to identify trend using 50 day average

You can calculate anything you are interested of.  Some examples:

  • Trend (Up or down) using what ever range you want.
  • Identify changes from one day to another in the stock price trend.
  • Breakouts. That is if stock price is above the highest price or below the lowest price for a given range of days.

I am going to calculate 50 day average for today and yesterday to identify each stock trend.

  1. Click Sheet "Calculation"
  2. Select and type in cell H2:=IF(AVERAGE(E2:E51)>AVERAGE(E3:E52),"UP","DOWN") + ENTER

The formula calculates if the trend is Up or Down using a 50 day average.

Iterate through each stock ticker using vba

  1. Click "Developer" tab How to show the Developer tab or run in developer mode
  2. Click "Visual Basic"
  3. Create a "Module" for your workbook How to Copy Excel VBA Code to a Regular Module
  4. Copy this vba code into module:
     

    Sub LoopAllTickers()

    On Error Resume Next

    Application.ScreenUpdating = False

    Dim LookUpTicker        As Range
    Dim TableStocks          As QueryTable

    Set LookUpTicker = Worksheets("Overview").Range("B4")
    Set TableStocks = ThisWorkbook.Worksheets("Calculation").QueryTables(1)

    '   Loop through list of stocks
    Do While LookUpTicker <> ""

    With TableStocks
    .Connection = "URL;http://finance.yahoo.com/q/hp?s=" & LookUpTicker
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "20"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    End With

    Sheets("Calculation").Range("H2").Copy
    LookUpTicker.Range("B1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    ' Move to next cell in list of stocks
    Set LookUpTicker = LookUpTicker.Offset(1, 0)

    Loop

    Application.CutCopyMode = False

    End Sub

Run your macro

  1. Press Alt + F8
  2. Double click on "LoopAllTickers"

You can add as many stock tickers as you like. The macro iterates through each ticker as long as there are no blank cells between.

Download excel sample file for this tutorial.

Remember to enable macros.

Identify stock trends.xls
(Excel 97-2003 Workbook *.xls)

Related posts:

Excel udf: Import historical stock prices from yahoo – added features

Excel udf: Import historical stock prices from yahoo

Learn how to create a stock chart in excel

Adjust stock chart axis automatically

Compare your stock portfolio with S&P500 in excel