'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Get Dividend Data When Excel Stocks Doesn't Show It
Question details
The user needs a way to pull dividend and yield information into their spreadsheet because the native Excel Stocks data type does not provide it.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Building and tracking an income-focused financial portfolio.
- Observed behavior
- The built-in Excel Stocks data type provides basic stock prices and related market information but fails to include dividend or yield data for securities.
Ensure you have an active internet connection to access external financial websites and verify that your version of Excel supports Power Query (Get & Transform Data) for external web connections.
Use Power Query to Import Financial Data from the Web
Power Query allows you to extract structured table data (including dividend yields) from reliable financial websites directly into your workbook.
Since the native Excel Stocks feature lacks comprehensive dividend yields, utilizing Power Query is the most robust workaround. It establishes a live connection to public financial portals.
Open your web browser and find a trusted financial website that clearly lists the dividend data in a table format (e.g., Yahoo Finance).
In Excel, navigate to the Data tab on the ribbon and click on 'From Web' in the Get & Transform Data group.
Paste the URL of the financial webpage containing your required dividend data into the dialog box and click OK.
In the Navigator window, select the specific table containing the dividend information from the list, then click 'Load' to import it into your worksheet.
Install a Third-Party Financial Excel Add-in
Dedicated financial add-ins can bypass the limitations of Excel's native features by connecting directly to professional stock market APIs.
Submit a Feature Request to Microsoft
Report the missing dividend feature directly to Microsoft to help prioritize future updates to the Stocks data type.
Try WPS Office for Your Financial Spreadsheets
If you are frustrated by the limitations of built-in features in Microsoft Excel, consider switching to WPS Office. WPS Spreadsheet is a fast, highly compatible, and lightweight alternative that excels at data analysis, handling complex financial formulas, and managing robust portfolio trackers without the hefty subscription fees.
- 1. Download WPS Office: Visit the official WPS Office website to download and install the free software.
- 2. Open Your Portfolio: Launch WPS Spreadsheet and open your existing .xlsx portfolio trackers directly without losing any formatting.
- 3. Import External Data: Utilize the Data tab in WPS Spreadsheet to easily import external CSVs or web data to monitor your dividend yields.
Frequently Asked Questions
Why did my previous Yahoo Finance data scraping stop working in Excel?
Basic web scraping in Excel relies on specific HTML table structures. In spring 2024, Yahoo Finance updated its website architecture, which broke the existing data connections. When this happens, you must delete the old query and recreate it based on the new website layout.
Does the Excel Stocks data type include forward-dividend information?
No. The built-in Excel Stocks feature primarily focuses on retrieving current stock prices, market capitalization, and daily changes. It does not consistently provide dividend yields, history, or forward-dividend information for securities.
Are there more stable alternatives to Excel's native Stocks feature for tracking income portfolios?
Yes. Using Power Query to connect to stable financial APIs, utilizing specialized third-party financial add-ins from the Microsoft Store, or downloading bulk CSV histories from market platforms are much more reliable methods for income and dividend tracking.
Is Power Query available in all versions of Microsoft Excel?
Power Query (integrated as Get & Transform Data) is built into Excel 2016 and later versions for Windows. For Mac users, Power Query features are available but heavily depend on having a current Microsoft 365 subscription.
