Track your stock investments in excel and use a web query to import current stock prices from yahoo.

  • Setup excel sheet
  • Concatenate stock symbols
  • Import current stock share prices using a web query
  • Refresh web query to update stock prices

Download excel tutorial file: Track your stock portfolio.xls.

Setup excel sheet

Here is a picture of my excel sheet:

Formula in G2:

=D2*E2+F2 + ENTER

copied down as far as needed.

Formula in J2:

=INDEX(Sheet2!$C$3:$C$124, MATCH(Sheet1!B2, Sheet2!$A$3:$A$124, 0)) + ENTER

copied down as far as needed.

Formula in K2:

=J2-G2 + ENTER

copied down as far as needed.

Concatenate stock symbols

We need to concatenate ticker symbols in column B to be able to use them in a web query. I am using this simple user defined function that I have created in a previous post. I am using this udf in cell A1 on sheet2.

Formula in A1 on sheet2:

=ConcComma(Sheet1!B2:B6) + Enter

VBA

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

Import current stock share prices using a web query

  1. Select Sheet2
  2. Select cell A3
  3. Click "Data" tab.
  4. Click "From Web"

  5. In the address field type:http://finance.yahoo.com/q/cq?d=v1&s=["Quote","Quote"]
  6. Click yellow arrow to select the whole table.
  7. Click Import
  8. Select where you want to place the imported table (cell A3 on sheet2)
  9. Enter parameter value "Quote"
  10. Click "Select cell" symbol


  11. Click cell A1 on sheet2
  12. Enable "Use this value/reference for future refreshes"
  13. Click OK.

Refresh web query to update stock prices

  1. Click "Data" tab.
  2. Click "Refresh All"

Download excel sample file for this tutorial.

Remember to enable macros.

Track your stock portfolio.xls
(Excel 97-2003 Workbook *.xls)