Add stock prices to Excel | Tutorial
Updated: Apr 10, 2021
In seconds Excel 365 users can include constantly refreshed trading prices to their stocks and shares spreadsheets - and if you have one you know how difficult that has been.
Select a cell that identifies a stock. This can be the code the exchange uses to identify, the name of the company, or any other string you feel could be helpful.
Select the Data menu in the ribbon; and in the Data Types group click the 'Stocks' icon.
If there's any ambiguity Excel will present the Data Selector window, within which you may select the correct stock from a list. Typically you will find Excel presents the same company that has been listed on more than one exchange, around the world. If you cannot see the stock you want, then you can alter the search string for a better term, and try again.
Once a valid stock has been identified, the cell contents will be replaced by a 'Stocks' icon, followed by a unique descriptor.
In the cell, click on the stocks' icon to review the card. This shows all of the information that is available 'through' the cell. The card will vanish on the next click of the mouse.
You can resize the card with the handle at its bottom right corner; and you can scroll through the card by moving the vertical scroll bar, or with the cursor control arrows on the keyboard.
We have converted the cell to, 'Stocks' - one of two 'Linked' Data Types rolled out to Excel 365 during 2020. The other is 'Geography', of which you can read more in a separate post (see 'related posts', below).
Linked data types connect a cell to an external database, and makes all of that linked data accessible via the cell. Assuming the cell you've converted is the top-left (A1) cell in your worksheet, try finding an empty cell elsewhere, and try the following:
=A1.Exchange=A1.Change * 12345=Index(A:A,1).HighThe information that is available varies from exchange to exchange and from stock to stock, but the auto-complete function will only show the fields that are available. If you enter something for which there is no data, you will get a #FIELD! error.
The final point to note is that prices are not updated automatically. There are updated: (a) when you open your spreadsheet; and (b) when you click Refresh All on the data menu. And, depending on the policy of the stock exchange, prices are likely to have been delayed by 15 minutes.
Download Sample Workbook
Typically you will have more than one stock in your spreadsheet, probably listed in the column of a table. But you won't want to play around with your live spreadsheet, and so the remainder of this tutorial is based on the following sample workbook.
Stocks Data Type
.xlsx
Download XLSX • 21KB
Download and open, "Stocks Data Type.xlsx", above.
This sample workbook shows the holdings of six stocks, all of which are listed on the London Stock Exchange.
Convert all stocks to the 'Stock' data type
Select all of the stocks
Click the Stocks icon in the Data Type group in the Data menu
Excel will try to match each of the selected stocks against it's database from sixty exchanges. Because companies are listed on more than one exchange, Excel may ask you for help and present a Data Selector panel.
In this example, Excel has matched two correctly, and there are, "4 remaining". The first is simple to resolve as the London Stock Exchange, is listed first, so you can simply 'Select' the first option.
As each item is resolved the number remaining will reduce and the data selector will move on to the next item. You can click on the left and right arrows to move forward and backward through the list of unresolved items. When there are no more unresolved items remaining the Data Selector will disappear, or you can close it whilst there remain unresolved items.
Sometimes the option you want is not shown, in which case you should enter a different string in the Data Selector's search bar, and click the search icon to try again.
If an item has been resolved incorrectly, then the Data Selector can be shown by right clicking on the 'Stock' icon in the left of the cell and then selecting, "Data Type", and, "Change".
Now, resolve all of the 'Stocks'.
Using Stock Prices in a formula
Having resolved all the stocks you can use any of the connected fields in a formula; and we will start with the one that's most compelling - the price, which well use in calculating the value of each stock holding.
In one of the cells in the Value column, enter the following formula:
=[@Holding]*[@Stock].price/100Note. We divide by 100 as London publish prices in pence, rather than pounds. We can confirm this through the currency field (.Currency) which will show, "GBp".
Count, Sum and Average
Functions such as COUNT, SUM and AVERAGE can work on individual fields. You may, for example, want to know the total market capital of the stocks in which you're invested.
In a space below the table, enter the following formula
=SUM(Table1[Stock].[Market cap])Unfortunately, the use of fields in such functions does not go much further. 'Stocks' is still very new to Excel and many functions do not work well with 'Stocks'. But Microsoft are working on it and much depends on: (a) when you're reading this; and (b) how recently you downloaded Excel 365.
An example of something that probably won't work for some time with Stocks, is the SUMIF function. Imagine you want to add the market capital from all the stock in the, "Oil & Gas", industry, then you might think to use the following:
=SUMIF(Table1[Stock].Industry, "Oil & Gas", Table1[Stock].[Market cap])Unfortunately, at the time of writing, this does not work.
Add Columns to Table
Because not all of the functions in Excel work with 'Stocks', or perhaps you want higher visibility for some of the fields, then you may want to create columns to show just one of the 'Stocks' fields.
You already know you can do this by adding columns to the table and writing a simple formula - but there's a quicker way.
Select a cell within the table
Click the 'Add Column' icon that appears to the right of the rightmost header.
Select the data you want to appear in the new columns.
Repeat as necessary.
Note. You may get #FIELD! errors if not all stocks in your table contain a value.
Sorting and Filtering
Now you have come more columns you may want to sort and filter on them, but you don't actually need those columns!
Turn Filtering ON in the Data menu
Click drop-down button in Stock header
Choose 'Industry' as the Selected field
As you can see, you can sort on filter on any of the fields attached to the Stock data type; and you can also see the range of industries you've invested in, in the bottom half of the sort and filter drop-down.
That's one less reason to turn fields into columns of their own, and this should help you to limit the widths of your tables.
STOCKHISTORY function
There is a function to show, for any given stock, the history of the prices. The STOCKHISTORY function released into Excel 365 after Stocks was released as a Data Type. So it's possible you have Stocks in your version of Excel, but not the STOCKHISTORY function. If you don't then, as a 365 subscriber, you can download the latest version of Excel for free.
Select the Stock History workbook and note the following:
There are Named ranges for: MyStock, FromDate, ToDate, Interval, Headers and MyFormula.
The formula in MyFormula uses the STOCKHISTORY function, which used all of the other named ranges as parameters.
STOCKHISTORY is an array function, which means that instead of returning a single function, it returns an array of values, displayed across many cells.
The last 6 parameters in the STOCKHISTORY function identify the columns to report.
Try changing some of the values in the B column. The dates are just entered normally, but there's data validation on the other three fields, meaning you can simply select a value from a drop-down list.
Note on Array Functions
If you want to make use of the STOCKHISTORY function then you'll probably need to know a little more about array functions. The tutorial, "Dynamic Search As You Type". has much to say on this subject.