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)

Related posts:

1. Scan stock markets in excel
2. Dynamic stock chart in excel – Add date ranges
3. Create a dynamic stock chart using a web query and a drop down list in excel
4. How to create excel macro to color every second row
5. Create a dynamic border to your list using excel conditional formatting
6. Color every second row using dynamic conditional formatting in excel
7. Highlight duplicates using conditional formatting in excel
8. Highlight dates within a date range using conditional formatting
9. Highlight the second or more duplicates in two lists using conditional formatting in excel
10. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel