Neo Ledger

A Google Apps Script-based investment portfolio ledger that tracks securities, computes lot-based gains, and generates tax summaries. Supports both Google Sheets (production) and local Node.js testing.

Features

Core Functionality

• Lot Tracking (FIFO): Tracks individual purchase lots with cost basis, supporting:

  • Buy/Sell transactions
  • Stock splits
  • Transfers between accounts
  • Gifts between owners
  • Class reorganizations (e.g., Alphabet GOOGL→GOOG split)

• Realized Gains Computation: Calculates capital gains with:

  • Short-term vs Long-term classification based on holding period
  • INR cost basis tracking with FX rates
  • Per-lot gain/loss attribution

• Tax Summary: Aggregates gains by:

  • Financial Year (April–March)
  • Asset Class
  • Gain Type (STCG/LTCG)
  • Applies exemptions and tax rates from configuration

• XIRR Cashflows: Generates cashflow data for portfolio XIRR calculation using Google Finance prices

• Cash Balance Tracking: Maintains cash balances by account and currency

• RBI 180-Day Compliance: Tracks foreign income aging for regulatory compliance

• Sensitivity Analysis: Produces FIFO-ordered lot data and summary tables for tax-efficient cash-raising analysis

Portfolio Views

• India Portfolio & US Portfolio summary sheets
• Equity by Account QC (quality control) view

Project Structure

neo-ledger/
├── Code.js              # Main business logic
├── Helpers.js           # Utility functions & data access layer
├── csv-to-json.js       # Data conversion utility
├── download-sheets.js   # Pull Google Sheets data into local JSON files
├── test-local.js        # Local test runner (Node.js)
├── appsscript.json      # Google Apps Script manifest
├── data/                # Real data folder (gitignored)
│   └── ...              # Your personal portfolio data
└── tests/
    └── data/            # Dummy test data (committed to repo)
        ├── Trades.json
        ├── Securities.json
        ├── Entities.json
        ├── LotActions.json
        ├── Config.json
        ├── Assertions.json
        └── ...          # Output files regenerated by tests
    └── ...

Data Model

Input Tables

Table	Description
Config	Tax rules: holding periods, rates, exemptions by asset class
Entities	Owners, brokers, accounts
Securities	Security master with ticker, asset class, country
Trades	Buy/Sell transactions with quantity, price, FX rate
LotActions	Corporate actions: splits, transfers, gifts, reorganizations
CashMovements	Cash inflows/outflows with currency

Output Tables

Table	Description
Lots_Current	Open lots with cost basis and quantity
LotConsumes	Records of lot consumption (sales)
Gains_Realized	Computed gains with holding period classification
Tax_Summary_FY	Aggregated tax liability by financial year
Cash_Balances	Cash positions by account/currency
XIRR_Cashflows	Cashflows for XIRR calculation
RBI_180_Ageing	Foreign income aging buckets
QC_Equity_By_Account	Position reconciliation view
Sensitivity_Data	FIFO-ordered lot data for tax-efficient liquidation analysis
Sensitivity_Summary	Position-level sensitivity summary by owner and ticker

Usage

Google Sheets (Production)

1. Create a Google Sheet with the required input tables
2. Open Extensions > Apps Script
3. Copy Code.js and Helpers.js into the script editor
4. Run rebuildAllDerived() to compute all derived tables

Local Testing (Node.js)

1. Ensure sample data exists in the data/ folder as JSON files
2. Generate local declaration files from the JSON Schema registry:

npm run generate:types

3. Run the test:

npm test

4. Output files will be written to the `data/` folder

#### Using Test Data

The repository includes dummy test data in `tests/data/` for testing without personal data:

```bash
# Generate declaration files from the schema registry
npm run generate:types

# Run with test data (safe to commit results)
npm test

# Run with real data (default)
node test-local.js

# Run with a custom data folder
node test-local.js --data=path/to/data

The test data includes:

• Fake owners (ALICE, BOB) and brokers (BROKER1, BROKER2)
• Sample securities (AAPL, GOOG, GOOGL, HDFCBANK)
• Example trades with buys, sells, splits, and an Alphabet class reorganization
• Assertions to validate the test results

Running node test-local.js --test regenerates derived outputs inside tests/data/.

Generated Types

The repository includes a JSON Schema registry at schema/neo-ledger-data-model.schema.json.

• Run npm run generate:types to generate types/neo-ledger.generated.d.ts.
• Helpers.js uses the generated type map so readTable("Trades"), readTable("CashMovements"), and similar calls resolve to table-specific row arrays in the editor.
• Generated type files and schema files are local-only artifacts and are excluded from Apps Script uploads.

Apps Script Deployment with clasp

clasp is Google's command-line tool for managing Apps Script projects.

Installation

npm install -g @google/clasp

Authentication and Project Link

clasp login
clasp clone <scriptId>

• clasp login opens a browser for Google OAuth and stores the login locally in your user profile.
• clasp clone <scriptId> recreates the local project link file .clasp.json in this folder.
• If you run git clean -fdx, .clasp.json is removed because it is intentionally gitignored, so rerun clasp clone <scriptId>.

The Script ID can be found in your Apps Script project under Project Settings.

Push Local Changes to Google

Before pushing to Apps Script, regenerate types and run the local test flow:

npm run generate:types
npm test
clasp push

Uploads local .js and appsscript.json files to the Apps Script project.

You can also use the combined helper command:

npm run deploy:gas

This regenerates types and then runs clasp push.

Pull Remote Changes

clasp pull

Downloads the latest version from Google Apps Script to your local folder.

Open in Browser

clasp open

Opens the Apps Script editor in your default browser.

Useful Flags

• clasp push --watch – Automatically push on file changes
• clasp push --force – Overwrite remote without confirmation

Downloading Real Data for Local Testing

download-sheets.js pulls the live input tabs from the Google Sheet into local csv/ and data/ folders so you can run the Node.js test flow against real data.

One-Time Setup

1. Go to Google Cloud Console.
2. Create a project or select an existing one.
3. Enable the Google Sheets API.
4. Create an OAuth client ID for a Desktop app.
5. Download the OAuth client JSON and save it in the repo root as credentials.json.

Authorize and Download

node download-sheets.js

On first run, the script opens an OAuth flow in the browser. After you approve access, it stores a refresh/access token locally in token.json and reuses it for later downloads.

If You Ran git clean -fdx

• credentials.json and token.json are both removed because they are intentionally gitignored.
• token.json is disposable; it will be recreated the next time you authorize download-sheets.js.
• credentials.json must be downloaded again from Google Cloud if you no longer have a copy.

Usage Examples

node download-sheets.js
node download-sheets.js --sheet=Trades
node download-sheets.js --output=./my-data

Main Functions

Function	Description
rebuildLots()	Process trades and actions to build current lots
rebuildXIRRCashflows()	Generate XIRR cashflow records
computeRealizedGains()	Calculate gains from lot consumes
buildTaxSummaryByFY()	Aggregate tax summary by financial year
computeCashBalances()	Compute cash balances by account
computeRBI180DayExposure()	Track foreign income aging
buildEquityByAccountQC()	Build open quantity QC by owner/account/broker/security
buildSensitivityData()	Build sensitivity analysis data and summary outputs
rebuildAllDerived()	Run all computations in sequence

Configuration

Tax rules are configured in the Config table with fields:

• AssetClass: e.g., "Equity", "Mutual Fund"
• HoldingPeriod_LT_Days: Days to qualify for long-term gains
• LTCG_Tax_Rate: Long-term capital gains tax rate
• STCG_Tax_Rate: Short-term capital gains tax rate
• LTCG_Exemption_INR: Annual exemption amount

Local vs Production Mode

The IS_LOCAL flag in Helpers.js controls the execution mode:

• false (default): Uses Google Sheets APIs
• true: Uses local JSON files in data/ folder

Notes

• Cash_Balances is derived from explicit CashMovements rows plus account metadata in Entities; trade rows do not automatically create cash entries.
• In local mode, Sensitivity_Data is written as structured JSON with a meta block and FIFO-ordered lots. The exported lot rows include BuyDate to preserve liquidation order context.