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)