Scan stock markets in excel

Filed in Excel on Feb.07, 2010.

Email This article to a Friend

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

Select sheet "Calculation"
Select cell A1
Click tab "Data"
Click "From Web"
Copy http://finance.yahoo.com/q/hp?s=CAT into the address field and click "Go"
Click "Black arrow in yellow box" next to historical prices table
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.

Click Sheet "Calculation"
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

Click "Developer" tab How to show the Developer tab or run in developer mode
Click "Visual Basic"
Create a "Module" for your workbook How to Copy Excel VBA Code to a Regular Module
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

Press Alt + F8
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)

Share and Enjoy: