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

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 the 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.

TL;DR: download a working spreadsheet containing everything discussed below.

1. Activating Stocks Data Types

These data types are available with Microsoft 365 subscriptions. Some users on older semi-annual enterprise update channels may not see them until their Office build is updated.

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 converts the cell to a data type cell.
4. In the little popup menu to the upper right of the cell, select fields such as price, ticker, and last trade time.

2. Activating Currency Data Types

These data types are available with Microsoft 365 subscriptions. Some users on older semi-annual enterprise update channels may not see them until their Office build is updated.

1. Enter 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. As with Stocks, in the little popup menu to the upper right of the cell, select fields such as price, ticker, and 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:

• SegmentLedger — 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 specifies the grouping key (Symbol); the second supplies the values to aggregate (AdjQuantity and NetAmount). HSTACK combines those columns into a table for GROUPBY. Using #All includes the headings 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.

Download the working example.