Automated system for transforming daily NAV (Net Asset Value) reports into structured portfolio reports with a visual dashboard.
This system monitors Outlook for incoming daily report emails, extracts attachments, and automatically transforms them into formatted portfolio reports.
┌─────────────────────────────────────────────────────────────┐
│ OUTLOOK │
│ Monitors inbox for two specific email subjects │
│ Saves attachments when both emails arrive │
└─────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────┐
│ EXCEL │
│ Transforms NAV data into Dashboard + Stocks + Options tabs │
│ Creates visual charts and KPI summaries │
│ Outputs formatted report │
└─────────────────────────────────────────────────────────────┘
- Subject:
Mobius Emerging Opportunities Fund LP| Custom daily portfolio report MMDDYYYY - Required attachment:
Gain And Exposure_Custom_MOBIUS EMERGING OPPORTUNITIES FUND LP_MMDDYYYY.XLSX - Contains: Individual position data (stocks, options, quantities, P&L, weights)
- Subject:
Mobius Emerging Opportunities Fund LP| Daily Reports MMDDYYYY - Required attachment:
Gain And Exposure_MOBIUS EMERGING OPPORTUNITIES FUND LP_MMDDYYYY.XLSX - Contains: Fund-level performance (YTD Fund Return in cell K94)
| Row | Content |
|---|---|
| 1-3 | Empty/formatting (ignored) |
| 4 | Sub-headers |
| 5 | Column headers |
| 6+ | Position data |
| Column | Header | Description |
|---|---|---|
| A | Product Name | Security name (used to identify stocks vs options) |
| B | Ticker | Bloomberg ticker or OCC format for options |
| C | ISIN | Security identifier |
| D | Portfolio Weight % | Position weight |
| E | Unit Cost USD | Average cost basis |
| F | Today USD | Current price |
| G | % Daily Gain/Loss | Daily change |
| H | Contribution to Performance | Attribution |
| I | Total Cost USD | Total cost basis |
| J | Market Value USD | Current market value |
| K | Total Net P&L YTD | Year-to-date P&L |
| L | # of Shares | Position quantity (negative = short) |
C:\Mobius Reports\Transformed\Transformed_Portfolio_DD MMMM YYYY.xlsx
| Column | Header | Description |
|---|---|---|
| A | Name | Security name |
| B | Ticker | Bloomberg ticker |
| C | Portfolio Wgt | Position weight % |
| D | % Diff (Cost) | Gain/loss vs cost basis |
| E | Daily Chg % | Today's price change |
| F | Unit Cost | Average cost (USD, rounded to nearest dollar) |
| G | Current Px | Current price from NAV report (USD, rounded) |
| H | Total Cost | Cost basis (USD, rounded) |
| I | Mkt Value | Current value (USD, rounded) |
| J | P&L | Year-to-date P&L (USD) |
| K | Attribution | Performance contribution % |
Note: All currency values display as numbers only (no $ symbols). Headers indicate USD.
Separated into PUTS and CALLS sections:
| Column | Header | Description |
|---|---|---|
| A | Name | Option description (e.g., META 01/16/2026 PUT 700) |
| B | Quantity | # of contracts |
| C | Underlying Qty | Shares of underlying stock owned |
| D | % Hedged | Coverage ratio |
| E | Strike Px | Strike price (USD) |
| F | Underlying Px | Underlying stock price from NAV report (USD) |
| G | % Moneyness | ITM/OTM percentage |
| H | Expiry | Expiration date |
| I | Unit Cost | Average cost per contract (USD) |
| J | Total Cost | Total cost basis (USD) |
| K | Current Px | Current option price (USD) |
| L | Mkt Value | Current market value (USD) |
| M | P&L ($) | Year-to-date P&L (USD) |
Note: All currency values display as numbers only (no $ symbols). Headers indicate USD.
Added at bottom of Stocks tab:
- Total Portfolio Value
- NAV Per Share
- Fund Inception Date
- YTD Fund Return (from K94 of non-custom file)
- MTD Net Return
The transformed output matches the professional styling of the input files:
| Element | Style |
|---|---|
| Sub-headers | Navy blue background (#003366) with white text |
| Column headers | Bold, gray text (#595959) |
| Data rows | Alternating white (#FFFFFF) and light gray (#F2F2F2) zebra striping |
| Data text | Dark gray (#404040) |
| Borders | Light gray thin borders |
- Windows 10/11
- Microsoft Outlook (Classic/Desktop version, NOT "New Outlook")
- Microsoft Excel with macros enabled
Note: As of v5.3, Bloomberg Terminal is no longer required. All data comes from the NAV reports.
Important: The "New Outlook" does not support VBA macros. You must use Classic Outlook.
C:\Mobius Reports\
├── Incoming\ ← Attachments saved here
├── Transformed\ ← Output reports saved here
└── Archive\ ← For manual archival
- Open Excel
- Press
Alt + F11(or Developer → Visual Basic) - Insert → Module
- Paste contents of
BloombergDataTransformer.vba - Save as
C:\Mobius Reports\Portfolio Transformer.xlsm(Macro-Enabled Workbook)
- Open Outlook (Classic version)
- Enable Developer tab: File → Options → Customize Ribbon → check Developer
- Click Developer → Visual Basic
- Double-click
ThisOutlookSessionin Project Explorer - Paste contents of
OutlookEmailMonitor.vba - Press
Ctrl + Sto save - Enable macros: File → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
- Restart Outlook
- In Outlook, press
Alt + F8 - Select
RunManualTest→ Run - You should see a status popup confirming the monitor is active
Once set up, the system runs automatically:
- Both daily emails arrive in your inbox
- Outlook detects and saves the attachments
- Excel transformation runs automatically
- Output saved to
C:\Mobius Reports\Transformed\
To process emails manually:
- Select an email in Outlook
- Press
Alt + F8→ProcessSelectedEmail→ Run
To test or run the transformation directly:
- Open the Custom NAV file from
C:\Mobius Reports\Incoming\ - Make sure
Portfolio Transformer.xlsmis also open - Press
Alt + F8→TransformBloombergData→ Run - Output appears in
C:\Mobius Reports\Transformed\
The system handles FW:, Fwd:, and RE: prefixes (including multiples like FW: FW: FW:), so you can test by forwarding old emails to yourself.
| Macro | Purpose |
|---|---|
RunManualTest |
Check if monitor is active and configured |
ProcessSelectedEmail |
Manually process a selected email |
ShowTrackerState |
See which emails have been tracked |
CheckFolderContents |
List files in Incoming folder |
ClearIncomingFolder |
Delete all files in Incoming folder |
ResetTracker |
Clear email tracking state |
| Macro | Purpose |
|---|---|
TransformBloombergData |
Run the transformation manually |
File → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
Run InitializeMonitor macro manually (Alt + F8)
- Check subject line matches expected pattern exactly
- Run
ShowTrackerStateto see what's being tracked - Try
ProcessSelectedEmailon the email directly
- Underlying prices are looked up from the stock positions in the same report
- If an option's underlying stock is not in the portfolio, the underlying price will be blank
- Ensure both files are in
C:\Mobius Reports\Incoming\ - Ensure
Portfolio Transformer.xlsmexists inC:\Mobius Reports\ - Check that file names match expected pattern
| File | Description |
|---|---|
BloombergDataTransformer.vba |
Excel VBA - transforms NAV data |
OutlookEmailMonitor.vba |
Outlook VBA - monitors emails, saves attachments |
CLAUDE.md |
Technical documentation for AI assistants |
Default paths are configured in OutlookEmailMonitor.vba:
Private Const BASE_FOLDER As String = "C:\Mobius Reports"
Private Const INCOMING_FOLDER As String = "C:\Mobius Reports\Incoming"
Private Const TRANSFORMED_FOLDER As String = "C:\Mobius Reports\Transformed"
Private Const EXCEL_TRANSFORMER_PATH As String = "C:\Mobius Reports\Portfolio Transformer.xlsm"To change locations, edit these constants and re-import the VBA code.