kevinsivic/finance-dashboard
1
0
Fork 0
Code Issues 5 Pull requests Projects Releases Packages 2 Wiki Activity Actions

Spreadsheet import #3

New issue
Open
opened 2026-10-13 23:38:40 -04:00 by kevinsivic · 1 comment
kevinsivic commented 2026-10-13 23:38:40 -04:00
Owner
Copy link

One-off script that imports the "Annual budget - Balance Sheet" CSV as Balances (Source: "Balance sheet").

  • Reads the CSV and a mapping file from a git-ignored data/ directory. No account names or balances are committed; tests use a made-up fixture.
  • Mapping file gives each row its Group, Holder and FAFSA Class. Generate a first draft with best guesses and mark uncertain rows for human review.
  • Fix the sheet's errors deliberately rather than copying them: liabilities header 11-2024 vs assets 12-2024, stale "Accurate as of" note, #REF! cells.
  • Zero-balance accounts that have closed are imported as Closed with their history.
  • Run locally against the dev database only.
One-off script that imports the "Annual budget - Balance Sheet" CSV as Balances (Source: "Balance sheet"). - Reads the CSV and a mapping file from a git-ignored `data/` directory. No account names or balances are committed; tests use a made-up fixture. - Mapping file gives each row its Group, Holder and FAFSA Class. Generate a first draft with best guesses and mark uncertain rows for human review. - Fix the sheet's errors deliberately rather than copying them: liabilities header 11-2024 vs assets 12-2024, stale "Accurate as of" note, `#REF!` cells. - Zero-balance accounts that have closed are imported as Closed with their history. - Run locally against the dev database only.
kevinsivic commented 2026-10-13 23:49:04 -04:00
Author
Owner
Copy link

Code is done (parser, mapping draft, importer, CLI). What's left needs a human.

Already done locally: your sheet is copied to data/balance-sheet.csv and a draft is in data/mapping.json (both git-ignored): 34 accounts, 30 items to review.

To finish:

  1. Edit data/mapping.json. Fix any wrong guess, then delete each review entry you've checked. Most are "Holder: guessed Joint". Also check:
    • the 12-2024 vs 11-2024 column (snapshot dates are YYYY-MM-01; change any you like),
    • the closed guesses (IL 401K, Centene 401K, LeanDog 401K, Honda Loan),
    • Emma's and Maddy's 529 FAFSA Class (the sheet leaves them untagged, but parent-owned 529s are reportable),
    • Chase (JEM Software) 03-2023: it equals the personal Chase card exactly and the sheet's own totals leave it out. Probably a copy error, so fix the cell in the CSV.
  2. Run make db-up, then cd server && DATABASE_URL=postgres://finance:finance@localhost:55432/finance npm run balance-sheet import ../data/balance-sheet.csv ../data/mapping.json. The import refuses to run while any review item remains.

Verified: importing a flag-cleared copy into a scratch database reproduced the sheet's Net Worth to within $1–3 rounding at every snapshot except 03-2023, which is the Chase (JEM) cell above. A re-run creates no duplicates.

Code is done (parser, mapping draft, importer, CLI). What's left needs a human. **Already done locally:** your sheet is copied to `data/balance-sheet.csv` and a draft is in `data/mapping.json` (both git-ignored): 34 accounts, 30 items to review. **To finish:** 1. Edit `data/mapping.json`. Fix any wrong guess, then delete each `review` entry you've checked. Most are "Holder: guessed Joint". Also check: - the 12-2024 vs 11-2024 column (snapshot dates are `YYYY-MM-01`; change any you like), - the closed guesses (IL 401K, Centene 401K, LeanDog 401K, Honda Loan), - Emma's and Maddy's 529 FAFSA Class (the sheet leaves them untagged, but parent-owned 529s are reportable), - **Chase (JEM Software) 03-2023**: it equals the personal Chase card exactly and the sheet's own totals leave it out. Probably a copy error, so fix the cell in the CSV. 2. Run `make db-up`, then `cd server && DATABASE_URL=postgres://finance:finance@localhost:55432/finance npm run balance-sheet import ../data/balance-sheet.csv ../data/mapping.json`. The import refuses to run while any review item remains. **Verified:** importing a flag-cleared copy into a scratch database reproduced the sheet's Net Worth to within $1–3 rounding at every snapshot except 03-2023, which is the Chase (JEM) cell above. A re-run creates no duplicates.
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
No results found.
Labels
Clear labels   
needs-info

   
needs-triage

   
ready-for-agent

   
ready-for-human

   
wontfix
No labels
needs-info
needs-triage
ready-for-agent
ready-for-human
wontfix
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
kevinsivic/finance-dashboard#3
No description provided.