Author: Oscar Cronquist Article last updated on August 11, 2017

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