EGX Portfolio Valuation Warehouse
A daily-automated valuation system for a multi-owner Egyptian Exchange equity portfolio, built for a private client: scheduled scrapers → Google Sheets → BigQuery warehouse → SQL models for per-owner wallet values, dividends, and corporate actions.
Client confidentiality: no owner names and no portfolio values appear anywhere in this case study — the pipeline is the story.
01The Business Problem
A family-held equity portfolio on the Egyptian Exchange, split across multiple owners, was being valued by hand: look up each stock price, multiply by each owner's shares, adjust for dividends and corporate actions, repeat. Slow, error-prone, and always out of date — and EGX corporate actions (bonus shares, capital increases, subscription rights) quietly change share counts if nobody models them.
02The Pipeline
03What I Built
Scheduled Ingestion
A Google Apps Script scraper (Cheerio) pulls daily last-prices for 9 EGX tickers from mubasher.info into a Google Sheet on a time-based trigger — running with a clean error record.
Warehouse Load
Upload jobs stream the Prices and Portfolio sheets into BigQuery (stocks.prices, stocks.portfolio, stocks.symbols_info) via the Tabledata.insertAll API, also on triggers — spreadsheet convenience in front, warehouse rigor behind.
Valuation Modeling
SQL computes each owner's daily wallet value as the sum of price × shares across holdings; the workbook models dividends and coupons alongside.
Corporate-Action Handling
Bonus shares, capital increases, and subscription rights are modeled explicitly, so share counts and valuations stay correct through EGX events — the part manual tracking always gets wrong.