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
2. Click "Visual Basic"
3. Click "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.

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

Table of Contents

Extract negative values and adjacent cells (array formula)
Extract negative values and adjacent cells (Excel Filter)

Array formula in B23:

=INDEX($B$3:$D$18, SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER copied down as far as needed and then copied to the right as far as needed.

Download excel sample file for this tutorial.

extract negative values and adjacent cells.xls
(Excel 97-2003 Workbook *.xls)

Extract negative values and adjacent cells (Excel Filter)

1. Select B2:D18
2. Click "Data" tab
3. Click "Filter" on the Ribbon
4. Click Black triangle in cell D2

   

5. Select "Number Filters"
6. Click "Less Than..."

   
   

7. Type 0 (zero)
8. Click OK!

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference) returns the rownumber of a reference

COLUMN(reference)
returns the column number of a reference

In this post I will show you how excel can monitor stocks prices.

Table of contents

• Introduction
• Create stock quotes separated by comma (vba)
• Setup a web query to import stock prices from msn money
• Create stock alerts when a price is above/below criteria using conditional formatting

Introduction

In the sheet below are some random stock quotes (A6:A9) I will use to import prices. I have also created some random price alert ranges (C6:D9)

Create stock quotes separated by comma (vba)

To make the web query work I need to create a text string in cell E3 containing all stock quotes separated by comma. I can´t do that with the excel built in functions so I created this simple function ConcComma().

Option Explicit

Function ConcComma(Substrings As Range)

Dim CELL As Range

For Each CELL In Substrings.Cells

ConcComma = ConcComma & CELL.Value & ","

Next CELL

ConcComma = Left(ConcComma, Len(ConcComma) - 1)

End Function

How to Create Custom User Defined Excel Functions

Type in cell E3:  ConcComma(A6:A9) + ENTER

Setup a web query to import stock prices from msn money

1. Create a new Sheet "Web Query"
2. Select A1
3. Click "Data" tab.
4. Click "From Web"
5. In the address field type: http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=["QUOTE","Enter stock, fund or other MSN MoneyCentral Investor symbols separated by commas."]
6. Click yellow arrow to select the table.
7. Click Import
8. Select where you want to place the imported table
9. Click "Select cell" symbol
10. Click Sheet "Alerts" and click cell E3.
11. Enable "Use this value/reference for future refreshes"
12. Click OK.

Create stock alerts when a price is above/below criteria using conditional formatting

1. Sheet "Alerts" in cell E6, type: =INDEX('Web Query'!$D$4:$D$32;ROW(A1)) + Enter copied down to E9
2. Select D6:D9
3. Click "Home" tab
4. Click "Conditonal formatting" on the ribbon
5. Click "New Rule"
6. Click "Use a formula to determine which cells to format"
7. Type =$D6<=$E6 in formula field
8. Click "Format..."
9. Click "Fill" tab
10. Select a color
11. Click OK!

Conditional formatting cells C6:C9

1. Select C6:C9
2. Click "Home" tab
3. Click "Conditonal formatting" on the ribbon
4. Click "New Rule"
5. Click "Use a formula to determine which cells to format"
6. Type =$C6>=$E6 in formula field
7. Click "Format..."
8. Click "Fill" tab
9. Select a color
10. Click OK!

Download excel sample file for this tutorial.

Stock alerts_msn.xls
(Excel 97-2003 Workbook *.xls)

Copying or moving cells is probably one of the most common activities in excel. Here is how to quickly select cells and adjacent cells containing values. The selection ends when there are blank rows or columns.

This is useful when working with large ranges. Keep in min that the selection might also contain blank cells. See examples below.

Example 1

1. Select a cell somewhere in the range.

   
   

2. Press F5

   

3. Click "Special.."

   

4. Click "Current region"
5. Click "OK"

Example 2

1. Select a cell somewhere in the range.

   
   

2. Press CTRL + SHIFT + * (shortcut)

Create number sequences (Autofill)

Example 1

1. Type "1" in cell B3 + Enter
2. Right click on black dot and drag down as far as needed. (See arrow)

   

3. Click "Fill series"

Example 2

1. Type "1" in cell A3 and "2" in cell A4
2. Select A3 and A4
3. Left click on black dot and drag down as far as needed. (See arrow)

Create a repeating number sequence

In this example I am going to create a repeating number sequence 1, 2, 3, 4.

Formula in A9:

=IF(A8=4, 1, A8+1) + Enter copied down as far as needed.

Create a number sequence and restart when a cell value equals a criterion

In this example the number sequence restarts every time the adjacent cell value equals "D".

Formula in B17:

=IF(A17="D", 1, B16+1) + Enter copied down as far as needed.

Create a number sequence to count records by year and month (sorted list)

Formula in B27:

=IF(AND(YEAR(A27)=YEAR(A26), MONTH(A27)=MONTH(A26)), B26+1, 1) + ENTER copied down as far as needed.

Alternative formula:

=IF(TEXT(A26, "myyyy")=MONTH(A27)&YEAR(A27), C26+1, 1) + ENTER copied down as far as needed.

Create a number sequence to count records by year and month (unsorted list)

Array formula in  B32:

=SUM(IF(TEXT($A$32:A32, "myyyy")=MONTH(A32)&YEAR(A32), 1, 0)) + Ctrl + Shift + Enter copied down as far as needed.

Alternative formula in C32:

=SUMPRODUCT(--(TEXT($A$32:A32, "myyyy")=MONTH(A32)&YEAR(A32))) + Enter copied down as far as needed.

Create a number sequence to count records by year (sorted list)

Formula in B39:

=IF((TEXT(A39, "yyyy"))*1=YEAR(A38), B38+1, 1) + Enter copied down as far as needed.

Create a number sequence to count records by year (unsorted list)

Formula in B46:

=SUMPRODUCT(--(TEXT($A$46:A46, "yyyy")*1=YEAR(A46))) + Enter copied down as far as needed.

Create a number sequence to count individual products

Formula in B52:

=SUMPRODUCT(--(A52:$A$52=A52)) + Enter copied down as far as needed.

Create a number sequence to count records within specific price ranges

Price ranges (0-500, 501-1000, 1001-1500)

Formula in B58:

=SUM(--(MATCH(A58, {0, 501, 1001, 1501}, 1)=MATCH(A58:$A$58, {0, 501, 1001, 1501}, 1))) + Ctrl + Shift + Enter copied down as far as needed.

Create a number sequence to count records by individual products and years

Formula in C64:

=SUMPRODUCT(--(TEXT($A$64:A64, "yyyy")&(B64:$B$64)=YEAR(A64)&B64)) + Enter copied down as far as needed.

Count cells between two values in a column

Formula in D15:

=ABS(MATCH(C12, tbl, 0)-MATCH(C13, tbl, 0))-1 + ENTER

Named ranges

tbl (A1:A10)

Count cells between two values in a row

Formula in D23:

=ABS(MATCH(C20, tbl_row, 0)-MATCH(C21, tbl_row, 0))-1 + ENTER

Named ranges

tbl_row (A18:E18)

Download excel sample file for this tutorial.

formula to count cells between two values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

ABS(number)
Returns the absolute value of a number, a number without its sign.

Students are allowed to sign up to maximum two courses. How do I filter students who have signed up for more than 2 courses?

Answer:

Array formula in B25:

=INDEX(tbl, SMALL(IF((COUNTIF(tbl, tbl)>=3)*(ISTEXT(tbl))*NOT((COUNTIF($B$24:B24, tbl))), ROW(tbl)-MIN(ROW(tbl))+1, ""), 1), (SMALL(IF((COUNTIF(tbl, tbl)>=3)*(ISTEXT(tbl))*NOT((COUNTIF($B$24:B24, tbl))), ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1, ""), 1)-SMALL(IF((COUNTIF(tbl, tbl)>=3)*(ISTEXT(tbl))*NOT((COUNTIF($B$24:B24, tbl))), ROW(tbl)-MIN(ROW(tbl))+1, ""), 1))*16384) + CTRL + SHIFT + ENTER copied down as far as needed.

Named ranges

tbl (B3:D21)
What is named ranges?

Download excel sample file for this tutorial.

Return multiple values if above frequency criterion.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

SMALL(array,k) returns the k-th smallest row number in this data set.

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

COLUMN(reference)
returns the column number of a reference

ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE

NOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE

Table of contents

Filter unique distinct values using "contain" condition of a column in excel (array formula)
Filter unique distinct values using "contain" condition of a column in excel (Advanced filter)

Filter unique distinct values using "contain" condition of a column in excel (array formula)

Array formula in C2:

=INDEX(List, SMALL(IF(ISNUMBER(SEARCH($B$2, List))*NOT(COUNTIF(C1:$C$1, List)), ROW(List)-MIN(ROW(List))+1, ""), 1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Named ranges

List (A2:A20)
What is named ranges?

Download excel sample file for this tutorial

Filter unique distinct values using contain condition from a column.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

NOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE

Filter unique distinct values using "contain" condition of a column in excel (Advanced filter)

1. Select the range (A1:A20)
2. Click "Data" tab on the ribbon
3. Click "Advanced" on the ribbon

   

4. Click "Copy to another location"
5. Select A1:A20 in "List range:"
6. Click C1 in "Copy to:"
7. Click "Unique records only"
8. Click OK!

   

9. Select C2
10. Click "Filter" on the ribbon
11. Click "Black triangle" in cell C1

    

12. Click "Text Filters"
13. Click "Contains..."

    

14. Type "r" in "contains" window
15. Click ok!

Download excel sample file for this tutorial

Select Sheet "Adv. Filter

Filter unique distinct values using contain condition from a column.xls
(Excel 97-2003 Workbook *.xls)

Array formula in D12 (cell references):

=INDEX($D$4:$D$8, SMALL(IF(($B$4:$B$8<$D$10)*($C$4:$C$8>$D$10), ROW($D$4:$D$8)-MIN(ROW($D$4:$D$8))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in D12 (named ranges):

=INDEX(Value_col, SMALL(IF((Rng_1<Inp_val)*(Rng_2>Inp_val), ROW(Value_col)-MIN(ROW(Value_col))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER copied down as far as needed.

Named ranges

Value_col (D4:D8)
Rng_1 (B4:B8)
Rng_2 (C4:C8)
What is named ranges?

Download excel sample file for this tutorial

Return multiple values if in range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

Question:

Hi,

What type of formula could be used if you weren't using a date range and your data was not concatenated?

ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 and Range2

Range1 Range2 Value
1.33 1.66 A
1.67 1.99 B
2.00 2.33 C

Answer:

Formula in D10:

=INDEX(D4:D6, SUMPRODUCT(--($D$8<C4:C6), --($D$8>B4:B6), ROW(A1:A3))) + ENTER

Download excel sample file for this tutorial

Return value if in range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference) returns the rownumber of a reference

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays