|

Excel: Stocks Data Type

By Bill Jelen
January 1, 2019
9 comments

Office 365 subscribers will notice a new Stocks data type appearing on the Excel data tab. With it, you can get current data from the internet related to companies from 60 different stock exchanges around the world. Using formulas, you can keep track of their latest stock price, trading volume, and other information.

 

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:

  1. Save your workbook with an XLSM extension.
  2. From Excel, press Alt+F11 to open the VBA editor.
  3. In the VBA editor menu, choose Insert, Module.
  4. 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

  1. Press Alt+Q to close VBA and return to Excel.
  2. Add a text box to your worksheet with Insert, Shapes, Text Box.
  3. Add text, such as “Start Refresh,” to the text box.
  4. Right-click the text box. Choose Assign Macro. Select RefreshAndSchedule.
  5. 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.
  6. 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.
  7. 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.

 

Bill Jelen is the author of MrExcel XL: 40 Greatest Excel Tips. Send questions for future articles to IMA@MrExcel.com.
9 + Show Comments

9 comments
    Kristian July 1, 2019 AT 4:17 am

    Hello, I’ve been using this function for a while now. When I opened the spreadsheet today, the language was changed from english to chinese for all my stocks. Please help me!
    Kind regards, Kristian

    Bill Jelen June 24, 2019 AT 7:03 am

    For William E and anyone else trying to copy the code from the article and pasting into Excel VBA. The article is using Typographers quotes that bend left or right. You need to change these in VBA to a regular apostrophe and regular quotes. There is one occurrence of each:
    ‘ Schedule Refresh 0 hours, 1 minute, 0 seconds
    Application.OnTime NextTime, “RefreshAndSchedule”

    Bill Jelen June 24, 2019 AT 6:40 am

    Tony – you can use IXIC or NASDAQ to return the Nasdaq index. For FTSE, try UKX or FTSE. For the Dow 30, use DJI.

    William Ellison June 20, 2019 AT 2:39 pm

    Trying to get the auto-refresh enabled, but I get a Compile error: Syntax error when I try begin the refresh process. I copied the text exactly.

    Tony June 18, 2019 AT 11:51 am

    Can’t find a way of including indexes like Nasdaq and FTSE

    Bill Jelen April 11, 2019 AT 7:28 am

    I have an update on VEA from the Excel team at Microsoft. They recently tweaked support for ETF’s. The data type will first try to find context from the surrounding cells. If they can’t derive context, then they will return the stock with the highest volume. To prevent the volume algorithm, enter this in cells A1:A8: Ticker, SPY, EEM, XLF, IVV, TLT, HYG, VEA. Then, convert to Stock. Because VEA is surrounded with other ETF’s from ARCA, Excel will correctly recognize it as the Vanguard fund. Once it is recognized, you can delete A2:A7 and have just the correct data type.

    Bill Jelen April 9, 2019 AT 10:35 am

    For Geoff: I’ve reported the issue with VEA to Microsoft so they can investigate it. For Charlie: Excel 2019 was created for people in top-secret government bunkers who do not have access to the Internet. Since the feature requires the Internet, it is not included. I think that Microsoft Marketing did a bad job of conveying that Office 2019 is a limited version of Office. When you are shopping at the Microsoft Store, there is nothing on the box saying that Office 2019 is not the full version of Office.

    Geoff April 8, 2019 AT 7:09 pm

    Great article. Thank you. But I cannot force Excel to find the following fund Vanguard FTSE Developed Markets ETF (VEA) on the ARCA exchange. Excel keeps returning VEA as Viva Energy Group LTD on the ASX. Suggestions?

    Charlie Brown February 13, 2019 AT 6:42 am

    Interestingly, these features are absent in Excel 2019. You’d think a 2019 version of the software would have the latest features.

You may also like