How to Import Stock Data into Excel 2016 (Without Stocks Data Type)
Question details
The user needs to import financial market information, such as prices and 52-week highs/lows, into an older version of Excel that does not support the modern Stocks feature.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Tracking stock prices, trading volume, and exchange details in a local spreadsheet for financial analysis or portfolio management.
- Observed behavior
- Excel 2016 lacks the built-in Stocks data type found in newer Microsoft 365 versions, preventing users from pulling live financial data directly through native data types.
Ensure you have an active internet connection and identify a reliable external stock data provider, such as Yahoo Finance or Google Finance, before attempting to import data.
Use Power Query to Connect to a Free Stock API
You can use the Get & Transform (Power Query) feature in Excel 2016 to fetch stock data from free financial APIs.
While Excel 2016 does not have a native Stocks data type, its Power Query feature can connect to external JSON or CSV endpoints provided by free market-data APIs (like Alpha Vantage or Yahoo Finance).
Sign up for a free financial API service like Alpha Vantage to get your unique API key and the URL endpoint for stock quotes.
In Excel 2016, go to the 'Data' tab on the ribbon, click on 'New Query' (or 'Get Data'), select 'From Other Sources', and then choose 'From Web'.
Paste your specific API URL (including the ticker symbol and your API key) into the URL box and click 'OK'.
The Power Query Editor will open. If the data is in JSON format, convert it to a table, expand the columns to show price and volume, and then click 'Close & Load' to place the data into your spreadsheet.
Download and Import Historical Data as a CSV
Many stock websites allow you to download current and historical market data as a CSV file, which can easily be opened or imported into Excel.
Use Google Sheets as a Workaround
Since Excel 2016 lacks a built-in stock function, you can use Google Sheets to fetch the data dynamically and then export the results to Excel.
Upgrade Your Spreadsheet Experience with WPS Office
If you find yourself limited by the features in older versions like Excel 2016, consider switching to WPS Office. It provides a modern, highly compatible alternative for managing financial spreadsheets, handling CSV imports, and organizing your data without requiring an expensive subscription.
Frequently Asked Questions
Why doesn't Excel 2016 have the Stocks data type?
The Stocks data type is a cloud-connected feature introduced exclusively for Microsoft 365 subscribers and Excel for the Web. It is not available in standalone, perpetual-license versions like Excel 2016 or Excel 2019.
Can I get real-time stock updates natively in Excel 2016?
Excel 2016 cannot natively stream real-time stock data. To get updated prices, you must rely on external third-party add-ins, Power Query API connections, or manually downloading and refreshing CSV files.
Are there free APIs available to pull stock data into Excel?
Yes, several financial data providers such as Alpha Vantage, Yahoo Finance, and Tiingo offer free API tiers. You can use Excel's Get Data feature to connect to these APIs and pull JSON or CSV data directly into your spreadsheets.
