GBM Investments

Goal

A system to manage and analyze investment portfolios held in GBM (a Mexican brokerage) accounts. It automates statement ingestion, transaction parsing, and portfolio analytics through a web dashboard. The system turns PDF documents into structured, queryable data, tracking movements across equities, funds, debt, FX, dividends, and tax withholdings in both the domestic (BMV) and foreign (SIC) markets.

How it works

1. Statement ingestion A script downloads the PDF statements from Google Drive using OAuth2 credentials with refresh tokens.
2. Parsing the GBM format A custom parser extracts movements, holdings, and tax data from GBM’s Mexican statement format.
3. Persistence in DynamoDB Movements and holdings are stored in DynamoDB tables organized by account and date.
4. Analytics export to S3 Data is exported to S3 as Parquet files partitioned by account, year, and month, ready for SQL queries via Athena.
5. Portfolio dashboard An Express API queries DynamoDB and Athena and serves a React frontend that displays portfolio analytics and reconciliation.

Data model

The system tracks movements (buy, sell, deposit, dividend, tax), ticker holdings (periodic snapshots by account, year, and month), and monthly aggregates (cash flows and performance metrics). Movement types span equities, funds, debt, FX, dividends, and tax withholdings.

AWS services

• DynamoDB Five tables: movements, file metadata, monthly summaries, tax records, and holdings snapshots.
• S3 Raw PDF storage and partitioned Parquet exports for analytics.
• Amazon Athena SQL engine querying the Parquet files via the Glue Data Catalog.
• Secrets Manager / SSM OAuth2 credential management plus account configuration and ticker classification.
• CloudFormation / CDK Infrastructure as code in TypeScript with CDK v2 for stack deployment.

External services

• Google Drive API PDF statement retrieval using OAuth2 refresh tokens.
• Banxico SIE API Inflation rate used to adjust and contextualize portfolio performance.

Stack