In this blog post I´ll show you how to update a stock chart in excel using a drop down menu and a web query connection.

• Create list of companies and corresponding tickers
• Create a dynamic range
• Create a drop down list
• Calculate dates
• Create a web query
• Create a macro to refresh web query and automate "text to columns"
• Create a stock chart
• Create a button

Here is a picture of the final result:

At the end of this post is an attached excel tutorial file.

Create list of companies and corresponding tickers

In this tutorial I have two sheets, Overview and Data. On sheet Overview I created a small list of companies and  corresponding ticker. You can search tickers at http://finance.yahoo.com/

Create a dynamic range

As I add new companies and tickers to the list I want the drop down list automatically updated. To accomplish this I need to create a dynamic named range.

1. Click tab "Formulas"
2. Click "Named ranges"
3. Click "New"
4. Name your range (formula in this tutorial).
   I named it "Dynrange".
5. Type in "Refers to:" =OFFSET(Overview!$B$3, 0, 0, COUNTA(Overview!$B:$B)-1, 1)
6. Click OK!

Create a drop down list

1. Select cell E3
2. Click "Data" tab
3. Click "Data validation"
4. Click "Data validation..."

   

5. Select Allow: List
6. Type in "Source:" =Dynrange
7. Click OK!

Calculate dates

To download the latest three months of historical data we need to calculate the the date three months back.

I did this calculation on sheet "Data". The formulas use the current date today automatically.

Cell H1 matches the corresponding ticker to the selected company in the drop down list.

See formula details on the attached file at the bottom of this post.

Create a web query

1. Click "Data" tab
2. Click "From web"
3. Paste this into address field:http://table.finance.yahoo.com/table.csv?a=["fm","fm"]&b=["fd","fd"]&c=["fy","fy"]&d=["tm","tm"]&e=["td","td"]&f=["ty","ty"]&s=["ticker", "ticker"]&y=0&g=d&ignore=.csv
4. Click "Go"
5. Click "Import"
6. Select cell "A10" on sheet "Data"
7. Click OK

   
   

8. Select cell E3 (fm) . Enable "Use this value/reference for future refreshes". Click OK.
9. Select cell E4 (fd) . Enable "Use this value/reference for future refreshes". Click OK.
10. Select cell E2 (fy) . Enable "Use this value/reference for future refreshes". Click OK.
11. Select cell B3 (tm) . Enable "Use this value/reference for future refreshes". Click OK.
12. Select cell B4 (td) . Enable "Use this value/reference for future refreshes". Click OK.
13. Select cell B2 (ty) . Enable "Use this value/reference for future refreshes". Click OK.
14. Select cell H1 (ticker) . Enable "Use this value/reference for future refreshes". Click OK.

VBA code to refresh web query and automate "text to columns".

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 Refreshwebq()

Set qryTableStocks = ThisWorkbook.Worksheets("Data").QueryTables(1)

With qryTableStocks
.BackgroundQuery = False
End With

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("Data").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

ActiveWorkbook.Connections("Connection").Refresh

Sheets("Data").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A10"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True

Sheets("Overview").Select

End Sub

Create a stock chart

1. Select High, Low and Close columns on sheet "Data"
2. Click "Insert" tab
3. Click "Other charts"
4. Click High-Low-Close stock chart
5. Cut and paste stock chart to sheet "Overview"
6. Right click on x-axis values
7. Click "Select data"
8. Click "Edit" in Horizontal (Category) axis labels
9. Select Dates on sheet "Data", =Data!$A$11:$A$71
10. Click OK!
11. Click OK!
12. Right click on x-axis values
13. Click "Format axis"
14. Click Axis Options
15. Click Axis Type: Text axis
16. Click Categories in reverse order
17. Click Vertical axis crosses: At maximum category
18. Click Close

Create a button and assign macro Refreshwebq()

How to Run an Excel Macro With a Worksheet Button

Download excel tutorial file

dynamic stock chart using web query.xls
(Excel 97-2003 Workbook *.xls)

Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different dates.

If I work with vlookup or Index-match I got only the first price for a certain securities. So I am not able to find the price of a securities that match both the name of the securities and the date.

Could you advice if there is any way to overcome?

Answer:

Here are two possible solutions:

• Create an excel 2007 table
• Use an array formula

The easiest is the excel 2007 table.

Excel 2007 table

1. Select the range
2. Click "Insert" tab
3. Click "Table"
4. Click OK
5. Click "black triangle" on Security header.
6. Select one or more securities
7. Click "black triangle" on Date header.
8. Select one or more dates

Array formula

Array formula in F9:

=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER copied right as far as needed and then copied down as far as needed.

Named ranges

tbl (B3:D19)
What is named ranges?

Download excel example file

Security.xlsx
(Excel2007  Workbook *.xlsx)

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

ROW(reference) Returns the rownumber of a reference

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

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

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

COLUMN(reference) Returns the column number of a reference

Question:

hi all,

thanks for the great formula/array formula. it works great.

lately, i noticed that the array formula will make the excel calculations CRAWL if I have thousands of entries (dates; but mostly repeated because of different product).

e.g.

1-Jan-2010 ProductA
1-Jan-2010 ProductB
1-Jan-2010 ProductC
1-Jan-2010 ProductD
2-Jan-2010 ProductA
2-Jan-2010 ProductB
2-Jan-2010 ProductC
...
...

the unique distinct values from the dates are 1-Jan-2010, 2-Jan-2010, and so on.

In a month (31-days) x 13 products = 403 entries. Multiply this to 12-months = 403 x 12 months ~est. 4.9k dates to be parsed.

the jackpot question: Is there a way to easily parse long list, without sacrificing performance (recalculations)?

thx!

A comment from this blog post: How to extract a unique distinct list of a column in excel

Answer:

I created a random list of 5000 values. Creating a unique distinct list using Advanced Filter only took a couple of seconds. Maybe that is fast enough?

I also tried sorting the list and using MATCH() (which is fast) to filter unique distinct values but that required a lot of manual work. Maybe someone else have some lightning fast formula technique?

I then created a macro, recording my actions using Advanced Filter. I edited the vba code and inserted a button (Form Control).

How to Run an Excel Macro With a Worksheet Button

Clicking the button refreshes the unique distinct list. Adding values to the list is no problem, the selection extends as long as there are no blank cells.

How to automatically use Advanced Filter to create a unique distinct list

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 AdvFilter()
'
' AdvFilter Macro

' Select first cell in column (Sheet1!A2)

Range("Sheet1!A2").Select

' Extending the selection down to the cell just above the first blank cell in this column

Range(Selection, Selection.End(xlDown)).Select

' Execute Advanced Filter on selection and copy to Sheet1!C2

Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Sheet1!C2"), Unique:=True

End Sub

Assign macro to button

How to Run an Excel Macro With a Worksheet Button

Download excel sample file for this tutorial.

Remember to enable macros.

quick unique distinct list.xls
(Excel 97-2003 Workbook *.xls)

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)

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.

You need to enable macros.

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