Author: Oscar Cronquist Article last updated on August 12, 2017

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

### 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

Recommended articles

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the MATCH function

Identify the position of a value in an array.

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.

Tip! Use the TEXTJOIN function to concatenate values, introduced in Excel 2016.

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 &amp; CELL.Value &amp; ","
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"