Using Excel’s Stock and Currency Data Types for Segment ledgers and Positions

By Gary Kennedy
October 22, 2025

Excel includes two powerful but often overlooked features: the Stocks and Currency data types. These allow you to pull delayed market and FX data directly into your spreadsheet — no plugins or VBA required.

In this article we’ll show how to:

1. Activate these data types.
2. Build a Segment ledger of trades using Babylon segment ledger format.
3. Form positions by grouping trades with Excel’s new GROUPBY function.
4. Value positions in local currency and convert them to USD using delayed FX rates.

TLDR, here is a working spreadsheet with everything discussed below.

1. Activating Stocks Data Types

The feature is available on Microsoft 365 subscriptions. Some users on older semi-annual update channels in enterprises might not see them until IT updates their Office build.

1. Type a MIC code and ticker separated by a colon into a cell (e.g. XLON:VOD or XJSE:STX40).
2. Select the cells, then go to the Data tab on the Ribbon.
3. In the Data Types group, choose Stocks which then converts the cell to a data type cell.
4. In the little popup menu to the upper right of the cell, select characteristics including price, ticker, last trade time.

2. Activating Currency Data Types

Similar to Stocks, this feature is available on Microsoft 365 subscriptions. Some users on older semi-annual update channels in enterprises might not see them until IT updates their Office build.

1. Type in the currency pair into a cell (e.g. GBPZAR).
2. Select the cell, then go to the Data tab on the Ribbon.
3. In the Data Types group, choose Currency.
4. Similar to stocks, in the little popup menu to the upper right of the cell, select characteristics including price, ticker, last trade time.

3. Build a Segment Ledger

We’ll follow the Babylon Segment Ledger specification, which standardises how to record trades. Key columns include:

• Segment — the segment name, constant across all rows.
• AccountAlias — user-friendly account label (AJBell-ISA, Investec-GIA).
• Type — Buy or Sell.
• Quantity — units traded.
• Symbol — the ticker.
• NetAmount — the total cash paid/received (including costs).
• Currency — the netAmount currency (e.g. GBP, ZAR).
• SettleDate — settlement date.
• Bourse — exchange code (e.g. LSE, JSE).

Example (excerpt from an LSE segment ledger):

4. Forming Positions with GROUPBY

Excel’s new GROUPBY function lets you aggregate directly, without PivotTables or helper columns.

On the Positions sheet:

=GROUPBY(SegmentLedger[[#All],[Symbol]],
		HSTACK(
			SegmentLedger[[#All],[AdjQuantity]], 
			SegmentLedger[[#All],[NetAmount]]
			),
		SUM,
		3,
		0,
		1)

The first parameter is what to group by (Symbol in our case), the second is what values to group (AdjQuantity and NetAmount). HSTACK is a relatively new function, it will horizontally stack columns to form a new table which can be convenient for functions on tables. The use #All ensures the headings are incorporated in the result.

Conclusion

With just a few clicks, Excel’s built-in Stocks and Currency data types can transform a simple trade ledger into a useful portfolio tool.

Record trades using Babylon segment ledger columns.

Aggregate positions with GROUPBY.

Pull delayed market prices and FX rates.

Convert everything to USD for consolidated reporting.

It’s a lightweight but powerful way to bring professional-style analytics into everyday spreadsheets.

A good working example is found here.