IBI Portfolio Dashboard

June 12, 2026 · View on GitHub

A Streamlit-based investment portfolio dashboard that reconstructs and tracks a multi-market portfolio from IBI broker (Israel) transaction exports. It processes 2,000+ raw Hebrew-labeled transactions, handles 21 transaction types with IBI-specific quirks (agorot pricing, phantom entries, option expiry reordering), and delivers 7 interactive tabs with 7 chart types, multi-currency accounting, and benchmark comparison against S&P 500 and TA-125.

Why

The Problem

Israeli investors using IBI (a leading Israeli brokerage) face a significant gap: the broker provides raw transaction exports in Excel with Hebrew labels, 21 distinct transaction types, prices in agorot (1/100 shekel), phantom internal entries, and no unified portfolio view across markets. Manually tracking holdings, cost basis, realized P&L, and multi-currency performance across both the Tel Aviv Stock Exchange (TASE) and US equity markets is error-prone and time-consuming.

What This Dashboard Solves

Pain Point	Previous Manual Approach	Dashboard Solution
Transaction classification	Manually map 21 Hebrew types	Automated classifier recognizes all types + cost basis
Agorot conversion	Error-prone mental math (÷100)	Normalized at ingestion boundary
Multi-currency P&L	Spreadsheet formulas with date-lookup	Historical FX rates cached per transaction date
Portfolio reconstruction	Start from scratch each time	Deduplicated, incremental inserts; sequential builder pass
Unrealized vs realized	Manual tracking spreadsheets	Automated calculations per position per sell
Phantom filtering	Delete suspicious rows manually	Regex pattern detection + symbol-based filtering
Performance metrics	No baseline (CAGR, Sharpe, etc.)	Built-in vs S&P 500 and TA-125 comparison
Options handling	Treat like stocks, causes errors	Short-sell support, expiry reordering fix
Pre-transfer shares	Unknown initial positions	Auto-phantom adjustment for shortfalls

IBI Broker Quirks & Design Decisions

Quirk	Design Decision	Trade-off
Agorot pricing — TASE prices in 1/100 shekel	Convert all TASE prices ÷100 at ingestion boundary	Detection logic required for API prices; one-time conversion at boundaries
21 Hebrew transaction types	Broker-agnostic classifier interface (`BaseClassifier` ABC) with IBI-specific implementation	Allows future broker integrations without touching builder/dashboard
Phantom entries — tax accounts (999*), forex (99028), settlement (5039813)	Mark `is_phantom=1` in DB, filter at query time	Preserves audit trail; allows re-examination if detection logic is wrong
Option expiry reordering — IBI records sell before credit	Expiry credits (`הפקדה פקיעה`) sorted after all removes (`_12` tier) so the short position exists when the credit arrives to close it	Transparent to downstream; sort key is string-based
Pre-transfer missing shares — sells exceed available qty	Auto-fill shortfall at cost basis ₪0	Early positions have artificially depressed cost basis
Stock splits (הטבה) — ambiguous: split if price=0, bonus if price>0	Inspect execution price to classify	Heuristic; breaks if IBI records split with non-zero price
Historical FX rates — reference date is last transaction date	Store per-date rates; no live FX for valuations	Reproducible valuations tied to data; no daily fluctuation surprises
Same-date ordering — buys and sells on same date	Process "add" before "remove" via sort key priority	Prevents false insufficient-shares errors
Two valuation methods — book value vs market value	"Invested Capital" charts use book value (cost basis + realized P&L); "Cumulative Returns" charts and performance metrics use mark-to-market	Clear separation: "How much did I deploy?" vs "How did it perform?"
No forward-looking data — charts end at last stored daily state	Historical series only; no live market value injection	Philosophically clean; avoids mixing methodologies

What

Dashboard Tabs (7)

Tab	Name	Layout	Key Content
1	Statistics	Two-column (1/3 stats, 2/3 charts)	Portfolio summary (6 metric cards + 3 position counts), performance metrics table (vs benchmarks), top 5 gainers/losers, currency exposure, treemap composition, P&L breakdown bar
2	Performance	Full-width, up to 8 charts	Total Return, CAGR, Max Drawdown, Sharpe Ratio metric cards; benchmark captions; invested capital area, drawdown, invested capital vs benchmarks, cumulative returns vs benchmarks (market value), US vs TASE splits (invested capital + cumulative returns), monthly returns bar, rolling Sharpe
3	Cash Flow	Full-width	Capital allocation (invested vs free cash per currency), external flows (deposits/withdrawals), investment income (dividends, realized P&L), charts and transaction history
4	TASE (₪)	Full-width	NIS positions with cash card, donut pie allocation, P&L bar chart, styled position table
5	US ($)	Full-width	USD positions with cash card, donut pie allocation, P&L bar chart, styled position table
6	Merged (₪)	Full-width	All positions in shekels (FX-converted), 3 cash cards (NIS, USD, total), unified pie + P&L bar, position table colored by market
7	Options	Full-width	Open/closed options with direction badges (LONG/SHORT/CLOSED), summary metrics, toggle for open-only filter + interactive table

Charts (7 Plotly Functions)

Chart	Function	Height	Description
Allocation Donut Pie	`allocation_pie()`	380px	Market value allocation with 35% hole, percent+label inside
P&L Horizontal Bar	`pnl_bar()`	dynamic (≥300px, scales with position count)	Color-coded gain/loss bars with currency labels
Portfolio Treemap	`allocation_treemap()`	450px	Hierarchical allocation colored by P&L %, multi-currency
Area with Gradient	`area_chart_with_gradient()`	420px	Portfolio value over time with gradient fill
Drawdown Underwater	`drawdown_chart()`	250px	Red underwater plot showing peak-to-trough decline
Monthly Returns Bar	`monthly_returns_bar()`	350px	Monthly returns with color-coded gain/loss bars
Rolling Sharpe	`rolling_sharpe_chart()`	300px	60-day rolling Sharpe with average line and reference lines

Performance Metrics

Metric	Formula	Notes
Total Return	$(\text{end} / \text{start} - 1) \times 100$	Market value (mark-to-market); falls back to book value if prices unavailable
CAGR	`(end / start)^(1/years) - 1`	Uses 365.25 days/year
Max Drawdown	`min((series - cummax) / cummax)`	Peak-to-trough decline %
Sharpe Ratio	$\text{mean}(\text{excess}) / \text{std}(\text{excess}) \times √252$	Risk-free rate = 3-month US T-bill (`^IRX`, cached daily via yfinance; falls back to 4%), min 30 data points

Supported Transaction Types (21)

Category	Types	Effect
Buys	קניה שח, קניה רצף, קניה מעוף, קניה חול מטח	Add shares, debit cash
Sells	מכירה שח, מכירה רצף, מכירה מעוף, מכירה חול מטח	Remove shares, credit cash, record realized P&L
Transfers	הפקדה (deposit), משיכה (withdrawal)	Share transfer in/out
Options	הפקדה פקיעה (expiry credit), משיכה פקיעה (expiry debit)	Option settlement with auto-reordering
Splits/Bonus	הטבה (price=0 → split, price>0 → bonus)	Adjust quantity, preserve cost basis
Dividends	דיבידנד, הפקדה דיבידנד מטח	Cash inflow (NIS or USD)
Fees/Tax	דמי טפול, משיכת מס חול מטח, משיכת מס מטח, משיכת ריבית מטח	Cash outflow (phantom)
Other	ריבית מזומן בשח (interest), העברה מזומן בשח (transfer)	Cash movements
Forex	B USD/ILS (symbol 99028)	NIS→USD conversion (phantom)

API Status — Twelvedata: Connected / Unavailable indicator
Import Transactions — File uploader for new .xlsx files (deduplicates automatically)
Last Import Info — Filename, timestamp, rows added
Force Re-parse Excel — Re-process the configured Excel file
Refresh Prices — Clear price cache and fetch current market data
Database Info — Path to .db, total transaction count

Limitations

Performance metrics use market value (mark-to-market) when price data is available, falling back to cost basis + realized P&L when insufficient prices are cached
Performance charts end at the last transaction date, not today's date
Sharpe Ratio uses the 3-month US Treasury bill rate (^IRX via yfinance, cached daily; falls back to 4% if unavailable) and requires ≥30 data points
Only 2 benchmarks supported (S&P 500, TA-125); adding more requires editing BENCHMARKS dict
FX conversion uses the rate from the portfolio state snapshot, not live rates
Options are not priced (no market price fetching for options)
Pre-transfer phantom shares are created at cost basis ₪0

How

Architecture

┌──────────────────────────────────────────────────────────────┐
│  Streamlit Dashboard (app.py) — 7 tabs                       │
│  statistics_view, performance_view, portfolio_view,           │
│  merged_view, options_view, cashflow_view                    │
│  theme, styles, charts, position_table, performance_metrics  │
├──────────────────────────────────────────────────────────────┤
│  Portfolio Engine                                            │
│  builder.py (sequential build + fast-load cache)             │
│  + ingestion.py (pipeline orchestration)                     │
├──────────────────────────────────────────────────────────────┤
│  Classification & Enrichment                                 │
│  ibi_classifier.py (21 types) + symbol_mapper.py             │
├──────────────────────────────────────────────────────────────┤
│  Market Data                                                 │
│  price_fetcher.py + fx_fetcher.py + benchmark_fetcher.py     │
├──────────────────────────────────────────────────────────────┤
│  Data Access (repository.py) + SQLite (db.py, 12 tables)     │
├──────────────────────────────────────────────────────────────┤
│  Input (excel_reader.py) + FX (fx_fetcher.py)                │
├──────────────────────────────────────────────────────────────┤
│  Domain Models: Transaction, Position (dataclasses)          │
└──────────────────────────────────────────────────────────────┘

Data Flow

IBI Excel (.xlsx)
    │
    ▼
Excel Reader ─── Parse Hebrew headers, sort by date, compute SHA256 row hash
    │
    ▼
IBI Classifier ── Classify 21 transaction types → effect + direction + cash flows
    │               Detect phantoms, normalize agorot prices (÷100)
    ▼
FX Fetcher ────── Fetch historical USD/ILS rates for all transaction dates
    │               Primary: Twelvedata API │ Fallback: yfinance
    ▼
Repository ────── Deduplicated insert (INSERT OR IGNORE by row_hash)
    │               Backfill fx_rate_on_date + cost_basis_nis per transaction
    ▼
Portfolio Builder ── Sequential pass over all transactions:
    │                  • Reorder option expiry (fix IBI ordering bug)
    │                  • Process buys/sells/splits/deposits chronologically
    │                  • Track NIS and USD positions separately
    │                  • Record daily portfolio state snapshots
    │                  • Fetch closing prices for market value calculation
    │                  • Calculate realized P&L on each sell
    │                  • Cache result in portfolio_current (JSON)
    ▼
Price Fetcher ──── Fetch closing prices for open positions
    │                Primary: Twelvedata │ Fallback: yfinance
    ▼
Streamlit Dashboard ── Render 7 tabs with metrics, tables, and charts
    Tab 1: Statistics — portfolio summary, performance, top gainers/losers
    Tab 2: Performance — historical returns vs benchmarks (up to 8 charts)
    Tab 3: Cash Flow — capital allocation, external flows, investment income
    Tab 4: TASE (₪) — NIS positions
    Tab 5: US ($) — USD positions
    Tab 6: Merged (₪) — all positions in shekels
    Tab 7: Options — open options positions

Key Algorithms

Sequential Portfolio Build (builder.py) — Processes every transaction chronologically in a single pass, maintaining position maps (positions_nis, positions_usd), cash balances, and cumulative realized P&L. Each sell calculates realized_pnl = qty × (sale_price - avg_cost) and proportionally reduces cost basis. NIS cash uses IBI's own running balance column; USD cash is accumulated from cash flow transactions. At each date boundary, a daily_portfolio_state row is written with both book value and market value.

Stock Split Handling — IBI records splits as the number of NEW shares added (הטבה with price=0). The builder computes new_quantity = current + added, keeping total_invested unchanged so average cost adjusts automatically: ratio = (pos.quantity + qty_abs) / pos.quantity.

Option Expiry Reordering (builder.py) — IBI records expiry credits (הפקדה פקיעה) after sells on the same date. The builder assigns sort keys in three tiers within a date: _10 (regular adds), _11 (regular removes), _12 (expiry credits). This guarantees the short position exists when the credit arrives to close it, and correctly skips credits with no prior short (orphan expiry). Options can have negative quantity (short positions), unlike stocks.

Past-Expiry LONG Detection (symbol_mapper.py, options_view.py) — When IBI omits the closing משיכה פקיעה for a long option, the builder cannot close the position. parse_option_expiry() extracts the expiry date from the option name (e.g. תP001560M407-35 → July 2024) using the embedded M[Y][MM] token. The Options tab uses this at render time to override past-expiry LONG positions to CLOSED.

Pre-Transfer Phantom Shares & Initial Positions — When a sell exceeds available quantity for a non-option position, the builder auto-fills the shortfall at cost basis ₪0. This handles shares that were bought before the IBI data begins and transferred in later. Known pre-export holdings with a real cost basis are instead seeded from config/initial_positions.json at the start of each build (NIS cost basis converted to USD via the FX rate near the earliest transaction date); the ₪0 phantom fill covers only the remaining small shortfalls.

TASE Symbol Resolution (symbol_mapper.py, tase_api.py) — IBI uses 5-8 digit numeric IDs for TASE stocks. Resolution chain: runtime cache → DB cache → static map (_KNOWN_TASE_MAP) → TASE website API (api.tase.co.il, no API key needed) → Twelvedata symbol_search API → fallback to None. Unresolvable IDs are remembered in the runtime cache to avoid repeated API calls. IBI abbreviates Hebrew fund names (e.g. "תכ." for "תכלית"), so a _HEBREW_ABBREVS lookup expands these before the Twelvedata search to improve match accuracy.

Stabilization Detection (performance_view.py) — Auto-trims the initial account build-up period where bulk imports create >10% daily swings. Uses pct_change().abs() <= 0.10 to find the first stable day and slices the series from there. Fallback: if no stable day found, keep all data.

Fast-Load Cache (repository.py, builder.py) — After each full build, the result (positions, cash, P&L) is serialized to JSON and stored in the portfolio_current table. On app startup, is_portfolio_stale() compares built_at against the latest import_log entry. If no new imports occurred, the cached result is returned instantly, skipping the full sequential rebuild. Position objects are serialized via to_snapshot_dict() and reconstructed via Position.from_dict().

Benchmark Caching (benchmark_fetcher.py) — S&P 500 and TA-125 prices cached in SQLite benchmark_cache table. Fetcher checks cached date ranges and only requests missing periods from yfinance, minimizing API calls. Failures degrade gracefully (logged as warnings).

Risk-Free Rate (benchmark_fetcher.py) — get_risk_free_rate() fetches the 13-week US Treasury bill rate (^IRX) via yfinance, caches the result in the metadata table for the current day, and falls back to 4% annual if the fetch fails. Used by both the Sharpe Ratio metric and the rolling Sharpe chart.

Database Schema (SQLite, 12 tables)

Table	Purpose
`transactions`	Classified transaction ledger (26 columns, deduped by `row_hash`)
`fx_rates`	Historical USD/ILS rates by date
`price_cache`	Market prices by (symbol, market, price_date)
`metadata`	App-wide key-value store (file mtime, last parse, risk-free rate, etc.)
`daily_portfolio_state`	End-of-day portfolio snapshot (invested, cash, P&L, market values per market)
`realized_trades`	Per-sell trade details with P&L
`portfolio_snapshots`	Point-in-time portfolio summaries
`position_snapshots`	Holdings within each snapshot
`tase_symbol_map`	IBI numeric ID → Twelvedata/yfinance ticker cache
`import_log`	Import history (file, timestamp, rows added/duped)
`benchmark_cache`	S&P 500 and TA-125 index prices for performance comparison
`portfolio_current`	Serialized build result for fast app startup (avoids full rebuild)

Technology Stack

Component	Technology
Web UI	Streamlit >=1.32.0
Data Processing	pandas >=2.1.0
Excel I/O	openpyxl >=3.1.2
Charts	Plotly >=5.18.0
Database	SQLite3 (WAL mode, built-in)
Price Data (primary)	Twelvedata (paid account)
Price Data (fallback)	yfinance >=0.2.66
HTTP	requests >=2.31.0
Config	python-dotenv >=1.0.0
Price Data (API client)	twelvedata

Color Theme

Semantic	Hex	Usage
Profit	`#10B981`	Positive P&L, gains
Loss	`#EF4444`	Negative P&L, losses
Accent Primary	`#6366F1`	Portfolio line, headers, TASE
S&P 500	`#F59E0B`	Amber dashed benchmark
TA-125	`#EC4899`	Pink dashed benchmark
Background	`#FAFBFE`	Page background
Text Primary	`#1E293B`	Main text

Getting Started

Prerequisites

Python 3.10+
An IBI broker transaction export (.xlsx file)

Installation

cd Portfolio_Dashboard
python -m venv venv
source venv/bin/activate    # Windows: venv\Scripts\activate
pip install -r requirements.txt

Configuration

Create a .env file in the project root:

TWELVEDATA_API_KEY=your_api_key_here    # Sign up at twelvedata.com (free tier: 800 calls/day)
YFINANCE_ENABLED=true                    # Free fallback, no API key needed

Optionally, holdings acquired before the first IBI export can be declared in config/initial_positions.json (symbol, quantity, NIS cost basis); the builder seeds them at the start of every build.

Input Data

Place your IBI Excel export at:

Portfolio_Dashboard/Trans_Input/Transactions_IBI.xlsx

The file should contain the standard IBI transaction export columns (Hebrew headers are auto-mapped).

Run

streamlit run app.py

Open http://localhost:8501 in your browser.

Importing New Data

Upload via sidebar: Click "Browse Files" under "Import Transactions" to upload a new .xlsx file. Duplicate rows are automatically skipped.
Force re-parse: Click "Force Re-parse Excel" to re-process the configured Excel file.
Refresh prices: Click "Refresh Prices" to clear the price cache and fetch current market data.

Project Structure

Portfolio_Dashboard/
├── app.py                          # Streamlit entry point (7 tabs)
├── requirements.txt                # Python dependencies
├── .env                            # API keys (not in repo)
├── docs/                           # Project documentation
│   ├── MASTER_PLAN.md              # Architecture & implementation plan
│   ├── performance-tab-why-how-what.md
│   ├── Insufficient_Shares_Investigation_2026-02-20.md
│   └── 2000_api_guide_eng.pdf      # IBI API reference
├── tests/                          # Test suite (145 tests)
│   ├── test_builder.py             # Portfolio build logic tests
│   ├── test_classifier.py          # Transaction classification tests
│   ├── test_excel_reader.py        # Excel parsing, date handling, row hashing tests
│   ├── test_ingestion.py           # End-to-end pipeline test (fixture Excel → build)
│   ├── test_performance_metrics.py # Metric calculation tests
│   ├── test_price_fetcher.py       # Agorot normalization + cache/option guard tests
│   ├── test_repository.py          # Database CRUD tests
│   ├── test_symbol_mapper.py       # Option detection + expiry parsing tests
│   └── test_tase_api.py            # TASE website API lookup tests
├── Trans_Input/
│   └── Transactions_IBI.xlsx       # IBI broker export
├── config/
│   └── initial_positions.json      # Pre-export holdings seeded into every build
├── scripts/
│   └── generate_presentation.py    # Screenshots all tabs → PowerPoint (dev utility)
├── data/
│   └── portfolio.db                # SQLite database (auto-created)
└── src/
    ├── config.py                   # Paths, API keys, constants
    ├── models/
    │   ├── transaction.py          # Transaction dataclass
    │   └── position.py             # Position dataclass (with to_dict/from_dict)
    ├── input/
    │   └── excel_reader.py         # IBI Excel parsing & normalization
    ├── classifiers/
    │   ├── base_classifier.py      # Abstract classifier interface
    │   └── ibi_classifier.py       # 21 IBI transaction type classifier
    ├── market/
    │   ├── symbol_mapper.py        # TASE ID → ticker resolution + Hebrew abbrev expansion
    │   ├── tase_api.py             # TASE website security lookup (no API key)
    │   ├── price_fetcher.py        # Market price fetching (Twelvedata + yfinance fallback)
    │   ├── fx_fetcher.py           # USD/ILS historical rate fetching
    │   └── benchmark_fetcher.py    # S&P 500 & TA-125 via yfinance + cache + risk-free rate
    ├── portfolio/
    │   ├── ingestion.py            # Full pipeline orchestration
    │   └── builder.py              # Sequential portfolio build loop + fast-load cache
    ├── database/
    │   ├── db.py                   # SQLite schema (12 tables, WAL mode)
    │   └── repository.py           # Data access layer (CRUD)
    └── dashboard/
        ├── theme.py                # Color palette, Plotly template (ibi_dark)
        ├── styles.py               # CSS stylesheet + HTML helpers (metric_card_html, html_table)
        ├── components/
        │   ├── charts.py           # 7 Plotly chart functions (pie, bar, treemap,
        │   │                       #   area, drawdown, monthly bar, rolling Sharpe)
        │   ├── position_table.py   # Styled HTML position table
        │   └── performance_metrics.py # CAGR, Sharpe, max drawdown, cumulative returns
        └── views/
            ├── statistics_view.py  # Tab 1: Two-column layout — stats + charts
            ├── performance_view.py # Tab 2: Up to 8 charts + benchmark comparison
            ├── cashflow_view.py    # Tab 3: Capital allocation + cash flow analysis
            ├── portfolio_view.py   # Tabs 4-5: Single-market (TASE or US)
            ├── merged_view.py      # Tab 6: All positions in ₪
            └── options_view.py     # Tab 7: Open options with long/short badges