How to Retrieve Stock Category or Investment Style in WPS Spreadsheets
Question details
A user needs to retrieve stock category or investment style information in Excel, but the Stocks data type does not expose these fields for individual securities.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is analyzing securities and wants to classify them by category or investment style using Excel data.
- Observed behavior
- Excel's Stocks data type provides market data such as price and market cap, but does not show Category or Investment Style fields for individual stocks.
Identify a common identifier, such as the ticker symbol or exact fund name, that exists in both your securities table and the reference data you will use for the lookup.
Retrieve stock category or investment style with a reference table and a lookup formula
Use this method when the Stocks data type lacks Category or Investment Style and you have provider-based reference data with a shared identifier.
Excel's Stocks data type usually returns market data for equities, while category and investment style are typically fund attributes supplied by the fund provider. You can create a reference table containing those descriptors and then retrieve them with XLOOKUP, VLOOKUP, or another lookup function.
In a new sheet, enter the security identifier such as Ticker or fund name in the first column, then add Category and Investment Style columns with the provider values.
In the main table, click the cell where Category should appear, go to Formulas > Lookup & Reference, and choose XLOOKUP. If XLOOKUP is not listed, choose VLOOKUP.
Set lookup_value to the identifier cell such as A2, lookup_array to the identifier column in the reference sheet, and return_array to the Category column, then press Enter.
Use the fill handle from the bottom-right corner of the formula cell to copy the formula down the column, and edit the return_array reference for the Investment Style column.
Use WPS Spreadsheets to Look Up Stock Category and Investment Style
Create a provider-based reference table in WPS Spreadsheets and use XLOOKUP or VLOOKUP to retrieve stock category and investment style values without manually re-entering fund data.
- 1. Create a reference sheet: Open WPS Spreadsheets, click the plus icon next to Sheet1, and enter Ticker, Category, and Investment Style headers with the provider values.
- 2. Enter the lookup formula: In the main sheet, click the Category result cell, go to Formulas > Lookup & Reference, and select XLOOKUP.
- 3. Map the identifier and return column: Select the stock identifier cell as lookup_value, the reference sheet identifier column as lookup_array, and the Category column as return_array, then press Enter.
- 4. Drag the formula down: Use the fill handle from the bottom-right corner of the formula cell to copy the formula to all rows.
- 5. Repeat for Investment Style: Change the return_array to the Investment Style column or use a second XLOOKUP formula to populate the adjacent column.
Frequently Asked Questions
Why doesn't Excel's Stocks data type show Category or Investment Style for stocks?
Category and investment style are primarily fund attributes, not equity attributes. Mutual fund providers often publish them, while Excel's Stocks data type focuses on market data such as price and market cap.
Does WPS Spreadsheets support XLOOKUP?
WPS Spreadsheets supports XLOOKUP in current versions. If XLOOKUP is not available in your version, use VLOOKUP with the lookup column positioned to the left of the return column.
Can I use VLOOKUP if my return column is not to the right?
VLOOKUP requires the return column to be to the right of the lookup column. If your layout does not allow that, use XLOOKUP or INDEX and MATCH instead.
How do I update category or investment style values later?
Update the reference table cells. The XLOOKUP or VLOOKUP formulas will recalculate and return the revised values automatically.
What identifier should I use for mutual funds?
Use the ticker, fund code, or exact fund name that appears in both your main table and the reference table so the lookup formula can match reliably.
