As with the new Geography data type (see the December 2018 column, http://bit.ly/2QNARuM), the Stocks data type isn’t supported for customers who purchased Excel 2019 or Excel 2016. Since the data comes from the internet, you must be an Office 365 subscriber in order to have access to these features.
SETUP
To begin, enter a list of company names or stock ticker symbols in some cells in Excel. Select those cells and set their data type to Stocks from the Data Types gallery in the Data tab of the Excel ribbon.
Excel will attempt to match each cell value to a company stock. The company name might change in the cell (e.g., “Microsoft” would become “Microsoft Corp”) and the bank icon—which looks like a building with Roman columns—will appear to the left of each company. This icon indicates that additional information is available for the data stored in the cell.
This data type works better with data stored in tables in Excel, so select one cell in your range of companies and press Ctrl+T to format the data as a table. In the dialog that opens, confirm your data has headers and click OK.
VERIFYING THE STOCK SYMBOLS
There are many similar stock symbols, so make sure Excel matched the company to the correct ticker on the correct exchange. In my experience, it’s typical to see as many as half of the company names I enter matched to the wrong exchange.
Select a cell with the bank icon, and a new Insert Data icon will appear to the right of the cell. Click the Insert Data icon for a list of available fields.
From the list, click on Exchange. A new column will appear in your table showing the exchange for each stock. Repeat this process to insert a column for Ticker symbol. For this article, I planned to show Barclays PLC, a stock traded under the BARC ticker symbol on the London Stock Exchange. Instead, Microsoft assumed I wanted BCS for the stock listed on the New York Stock Exchange (NYSE).
To override Excel’s default match, right-click a company and go to Data Type, Change.
A Data Selector panel will appear. In the case of Barclays PLC, the first three matches were BCS from the NYSE, BARC from the London exchange, and BCY2 from the Xetra trading venue. Click on the Select button for the desired stock.
STOCK DATA
As with the Geography data type, there’s a new formula syntax where you point to the cell containing a data type, then type a period and the field name in brackets. For example, if the value in cell A2 is Microsoft Corp., then the formula =A2.[Shares outstanding] will return the shares outstanding information for Microsoft stock. You can omit the brackets if the field name has no spaces or other punctuation. For example, =A2.Price and =A2.Change will both work without the brackets, but =A2.[P/E] and =A2.[Market cap] require brackets.
The following fields are available when using the Stocks data type: Ticker symbol, Exchange, Exchange abbreviation, Currency, Previous close, Open, Low, High, Price, Change, Change (%), 52-week low, 52-week high, Volume, Volume average, Shares outstanding, Market cap, P/E, Beta, Instrument type, Last trade time, Industry, Year founded, CEO, Employees, Description, Headquarters, and Name.
When you first use the Stocks data type feature in a workbook, a message appears in the information bar to warn that the financial market information is provided “as-is” and isn’t meant to be advice for trading purposes. Click the message to open the Help panel and learn about the current sources for the data. Currently, the data comes from investment research company Morningstar Inc.
The Help topic about data sources also contains a table that details the “Supported Exchanges and Delay Times.” Make the Help panel wider to reveal more columns in the table, including information on time zones and the data delay in minutes. Quotes from the NYSE are delayed 15 minutes, but quotes from other exchanges are from the end of the most recent trading day.
Excel won’t automatically recalculate the current stock price with each calculation of the workbook. Instead, you can click the Refresh All icon on the Data tab of the ribbon or right-click the cell containing the Bank icon and choose Data Type, Refresh.
A feature that’s missing is the ability to update the stock price every minute or every few minutes. You could use a VBA macro to click Refresh All every minute. To do so:
- Save your workbook with an XLSM extension.
- From Excel, press Alt+F11 to open the VBA editor.
- In the VBA editor menu, choose Insert, Module.
- Copy the following code and paste it in the blank module:
Sub RefreshAndSchedule()
ActiveWorkbook.RefreshAll
′ Schedule Refresh 0 hours, 1 minute, 0 seconds
NextTime = Time + TimeSerial(0, 1, 0)
Application.OnTime NextTime, ″RefreshAndSchedule″
End Sub
- Press Alt+Q to close VBA and return to Excel.
- Add a text box to your worksheet with Insert, Shapes, Text Box.
- Add text, such as “Start Refresh,” to the text box.
- Right-click the text box. Choose Assign Macro. Select RefreshAndSchedule.
- If you’ve never used macros before, go to File, Options, Trust Center, Trust Center Settings, Macro Security. Change the security setting to the second choice. Close and re-open Excel for the settings to take effect.
- Click the Start Refresh icon. Data will update immediately and then every minute after that point. Note that this feature would work best in a dashboard on an unattended computer. If you’re trying to use Excel while the Refresh macro is running, the pause it creates every minute will become annoying.
- The only way to stop the macro from running is to save the workbook and exit Excel.
The new Stocks data type is a great first iteration, but it doesn’t contain every data point that you might expect. There’s currently no support for historical stock trading information, except for the previous day’s closing price and the 52-week high and low prices. Also, there’s no good way to have the stock price automatically update every minute except for using a VBA macro. These features might come in the future.
Download a copy of the workbook used in this column: 01Excel2019--workbook.
SF Says: The Stocks data type is a great way to compare current stock prices for your company and competitors.
