Portfolio Bot

The Portfolio Bot tracks investment portfolios by splitting trade orders on Financial Books, mirroring checked quantities into a dedicated Portfolio Book, and calculating realized gains/losses using FIFO.

It works well for stocks and other traded instruments, and it can also split explicit interest amounts on trades, which is useful for bond-style dirty/clean price workflows. Broader coupon schedules, accrual calendars, tax logic, reconciliation flows, and report generation remain external to this bot.

How it works

The bot operates across one shared Portfolio Book and one or more Financial Books in the same Collection:

• Financial Book(s) — record money flowing in and out, typically one per currency
• Portfolio Book — tracks quantities (units bought, units sold) managed by the bot
• Base Book (optional but recommended for multi-currency FX separation) — one Financial Book marked with exc_base: true to receive exchange gain/loss entries

Use only one Portfolio Book per collection.

The lifecycle has three main stages:

1. Post — you post a purchase or sale order on a Financial Book. The bot splits it into fees, interest (if any), and the actual instrument trade.
2. Check — you check the instrument trade on the Financial Book. The bot mirrors the quantity to the Portfolio Book.
3. Calculate / Reset / Forward Date — from the Portfolio Book menu, you run FIFO calculations, rebuild/reset state, and optionally carry open positions forward.

In Bkper, any countable resource can move between accounts. On the Financial Book the resource is money; on the Portfolio Book the resource is quantity (shares or units). The transaction amount on the Portfolio Book is the number of units, while monetary values from the Financial Book are stored as transaction properties.

The bot auto-creates several support accounts (Unrealized, Realized, FX, Forwarded, MTM). Their account types affect how Bkper groups aggregate them:

• Asset / Liability → permanent balances; mixed groups display as Equity (gray).
• Incoming / Outgoing → period activity; mixed groups display as Net Result.

The Portfolio Book is bot-managed. Do not manually post or edit quantity transactions there. Use the Portfolio Book only to run the Portfolio Bot menu.

If the bot auto-creates a new instrument account on a Financial Book, assign that account to a group with stock_exc_code before checking the instrument trade. Otherwise, the Portfolio Book mirror will be skipped.

Purchase

You buy 1 share of GOOG for 165 with no fees. Post the order on the Financial Book, then check the instrument trade.

You post the purchase order:

05/06  165  Bank  >>  Broker  buy  instrument: GOOG  quantity: 1  trade_date: 05/07/2025

The bot splits (on TRANSACTION_POSTED) into a fees transaction if needed and the actual trade:

You check the instrument trade (Broker >> GOOG). The bot mirrors the quantity:

On the Portfolio Book, the Bkper transaction amount is the quantity in units. The original monetary amount from the Financial Book is preserved as the original_amount property.

The bot sets the instrument trade's Bkper date to the trade_date you provided. The original posting date is preserved as the settlement_date property.

The Portfolio Book now shows 1 unit of GOOG. The Financial Book shows 165 in GOOG.

Sale

You sell 1 share of GOOG for 180. Post the order, then check the trade.

You post the sale order:

05/15  180  Broker  >>  Bank  sell  instrument: GOOG  quantity: 1  trade_date: 05/16/2025

You check the instrument trade (GOOG >> Broker). The bot mirrors the quantity:

As with purchases, the Portfolio Book amount is the quantity in units; the monetary value is the original_amount property.

Fees and interest

When the order includes fees or interest, the bot posts separate transactions for each before the instrument trade.

• Fees go to the account defined by stock_fees_account on the broker/exchange account.
• Interest goes to or from <Instrument> Interest on the Financial Book.
• The instrument trade amount excludes explicit interest and reconstructs the trade amount apart from fees.

The bot auto-creates <Instrument> Interest as an Asset account. This models accrued interest as a recoverable position (prepaid/receivable), not as period income or expense. If you need it as Incoming/Outgoing, create the account manually before posting the first interest-bearing trade.

Purchase with fees:

05/06  175  Bank  >>  Broker  buy  instrument: GOOG  quantity: 1  trade_date: 05/07/2025  fees: 10

This same mechanism can be used for bond-style trades by passing explicit interest, but the bot does not manage coupon schedules or accrual calendars on its own.

Gain/Loss updates

The portfolio lifecycle involves four result layers:

1. Unrealized (Mark-to-Market) — periodic valuation adjustments, usually recorded externally
2. Realized — gains/losses recognized on sale, calculated by the bot using FIFO
3. Exchange — FX gains/losses separated on the Base Book when exc_base: true is configured
4. Period closing — by full liquidation in historical-only mode or by Forward Date in fair/both modes

Unrealized gain/loss (Mark-to-Market)

To periodically report the portfolio position, unrealized gains and losses are tracked on the Financial Book — typically once a month, prior to reporting. This adjusts each instrument's monetary value to reflect current market price.

Regular mark-to-market is usually done externally (for example with valuation spreadsheets) by recording transactions between the instrument account and its Unrealized account:

Unrealized accounts represent unsettled positions — you have not actually gained or lost anything until the instrument is liquidated. Conceptually this is a Liability (or contra-Asset), so the bot auto-creates ... Unrealized accounts as Liability by default. If you group a Liability-type GOOG Unrealized with Asset accounts, Bkper displays the mixed group balance as Equity (gray). If you prefer Unrealized to appear as period activity, create the first ... Unrealized account manually as Incoming before the bot runs.

The bot infers the type for new support accounts by scanning existing accounts with the same suffix in the book and picking the most common type. Because the default starts at Liability, you need at least two manually-created Incoming Unrealized accounts before the bot will switch to creating subsequent ones as Incoming.

For instruments with an explicit interest leg, the associated interest accounts (<Instrument> Interest / <Instrument> Interest Unrealized) follow the same pattern.

Optional auto-MTM during calculation

In the Portfolio Book menu, Calculate offers a Perform #mtm valuations? checkbox.

When enabled, the bot may create supporting bot-generated entries such as:

• #mtm
• #interest_mtm

These help align processed lots and clear residual interest balances during the realized-results workflow. They complement — not replace — your broader external valuation process.

Realized gain/loss

At sale, unrealized gains/losses become realized. The bot creates ... Realized and ... Realized Hist accounts as Incoming, because they represent recognized period activity. Open the Portfolio Book and select More > Portfolio Bot. Choose the account(s), set the date, and click Calculate.

The bot then:

• matches sales to purchases in FIFO order
• can split quantity transactions when only part of a lot is consumed
• stores lot-matching logs on the Portfolio Book
• records realized gain/loss transactions on the Financial Book

In Both mode, the bot also creates historical companion entries using separate historical accounts and hashtags:

• #stock_gain_hist
• #stock_loss_hist

Short sales

If a sale occurs before the covering purchase, the bot treats it as a short-sale workflow.

When the covering purchase arrives, the bot matches it back to the earlier sale, marks the Portfolio Book transaction with short_sale: true, and records the realized result on that coverage.

Exchange gain/loss (multi-currency)

When a Base Book is explicitly configured with exc_base: true, the bot separates exchange-rate variation from instrument-price variation. Realized FX gains/losses are recorded on the Base Book.

Unrealized FX accounts (... Unrealized EXC) default to Liability, just like regular Unrealized accounts. Realized FX accounts (... Realized EXC, Exchange_XXX) conceptually represent recognized period activity and should be Incoming, though the current type-inference behavior may group them together.

In Both mode, the bot also creates historical companion FX entries:

• #exchange_gain_hist
• #exchange_loss_hist

For reliable FX separation, explicitly configure one Financial Book with exc_base: true. The code does have some USD fallback lookups, but that is not the same as running in a fully configured Base Book mode.

Advanced FX routing options such as exc_aggregate and exc_account are described below.

Calculation models

The bot supports three calculation models controlled by Portfolio Book properties:

• Both (default) — calculates using both fair and historical cost basis. Creates additional historical result accounts such as Unrealized Hist, Realized Hist, and historical FX companions when applicable.
• Historical only (stock_historical: true) — uses only historical cost and rates.
• Fair only (stock_fair: true) — uses only fair/market cost and rates.

If neither stock_historical nor stock_fair is set, the bot calculates using Both.

Transaction cost properties are optional overrides, not mandatory requirements:

• cost_hist — overrides the historical local cost used by the historical branch
• cost_base — fixes a fair/base-currency cost so the bot can derive a specific fair exchange rate for the trade
• cost_hist_base — fixes a historical/base-currency cost so the bot can derive a specific historical exchange rate for the trade

If these overrides are omitted, the bot falls back to the trade values already stored on the instrument transactions and other available connected data.

Forward date

For Fair only or Both calculation models, open positions must be carried forward to the next period by setting a forward date in the Portfolio Book. This is not needed for historical-only books, where a period closes naturally when an instrument is fully liquidated.

Open the Portfolio Book, select the account(s), choose More > Portfolio Bot, set the date, and click Set Forward Date. The bot then:

1. copies each unchecked Portfolio Book transaction as a forward log entry
2. updates the original transaction's date, order, forward price, and forward exchange-rate properties
3. may create a liquidation bridge transaction in the Portfolio Book to carry open quantity to the new period
4. records a Forwarded Results transaction on the Financial Book to bridge the unrealized gap (fair/both only)
5. stores the new forward state on the account (forwarded_date, forwarded_price, forwarded_exc_rate)
6. sets a closing date on the Portfolio Book to one day before the forward date once all active accounts share that same forward date

The bot records forwarded results against a <Instrument> Forwarded account, which it creates as Liability by default.

After forwarding, future FIFO calculations use the forwarded valuation as the new baseline.

Forward-date restrictions

The bot blocks forwarding when:

• the account is flagged with needs_rebuild
• the account still has uncalculated results
• the requested forward date is equal to or earlier than the current realized date
• the requested forward date is the same as the current forwarded date

Lowering an already-set forward date is an advanced repair flow and requires:

• OWNER permission on the Portfolio Book
• an unlocked/unclosed collection

Configuration

# Group: NASDAQ (Financial Book)
stock_exc_code: USD

# Account: Broker (Financial Book)
stock_fees_account: Broker Fees

Operational constraints

Before running the bot in production, keep these operating rules in mind:

• all books must live in the same collection
• only one Portfolio Book should exist per collection
• users need EDITOR or OWNER permission in the relevant Financial Books to run calculations for those currencies
• the Portfolio Book must not have pending backlog tasks when starting Calculate, Reset, Full Reset, or Forward Date
• locked transactions, locked books, or closed books can block reset/calculate/forward flows
• Full Reset and lowering an existing forward date are reserved for stricter operational conditions described below

Advanced

Template

Explore the template books to see the Portfolio Bot in action:

• Financial Book (USD)
• Portfolio Book

To use the template, make a copy, place the books in a collection, and install the Portfolio Bot on all books.

Learn more

• Structuring Books & Collections — how bots connect books for consolidated reporting