- create user
- add portfolio
- add categories (added with portfolio, so map ids here)
UPDATE a SET old_acct_id = cat_id, old_parent_acct_id = parent
FROM accounts a
LEFT JOIN checkbook_categories cc
ON a.account_name = cc.category OR a.account_name LIKE CONCAT('%: ', cc.category)
WHERE portfolio_id = 0 AND cat_id IS NOT NULL
- fix mis-connected categories (Food, Ins, Dental)
UPDATE accounts SET old_acct_id=5, old_parent_acct_id=1 WHERE portfolio_id=7 AND l=16 -- Auto: Ins
UPDATE accounts SET old_acct_id=37, old_parent_acct_id=37 WHERE portfolio_id=7 AND l=91 -- Food
UPDATE accounts SET old_acct_id=43, old_parent_acct_id=42 WHERE portfolio_id=7 AND l=112 -- Health: Dental
UPDATE accounts SET old_acct_id=52, old_parent_acct_id=52 WHERE portfolio_id=7 AND l=143 -- Ins
UPDATE accounts SET old_acct_id=54, old_parent_acct_id=52 WHERE portfolio_id=7 AND l=144 -- Ins: Dental
UPDATE accounts SET old_acct_id=151, old_parent_acct_id=92 WHERE portfolio_id=7 AND l=230 -- Pet Care: Food
- add accounts
→$data array(38)
acct_id
portfolio_id
parent_acct_id
acct_name
acct_type
interest_rate
starting_balance
archived_balance
active
#1
"1"
"1"
"1"
"Main Street"
"checking"
"0.000"
"0.00"
"0.00"
"false"
#2
"2"
"1"
"2"
"Washington Mutual"
"credit"
"0.000"
"15700.00"
"0.00"
"false"
#3
"3"
"1"
"3"
"Capital One"
"credit"
"0.000"
"7000.00"
"0.00"
"false"
#4
"4"
"1"
"4"
"Discover"
"credit"
"0.000"
"3500.00"
"0.00"
"true"
#5
"5"
"1"
"5"
"American Express"
"credit"
"0.000"
"2000.00"
"0.00"
"false"
#6
"6"
"1"
"6"
"Busey Checking"
"checking"
"0.000"
"0.00"
"0.00"
"true"
#7
"7"
"1"
"7"
"Mutual Funds"
"investment"
"0.000"
"0.00"
"0.00"
"false"
#8
"8"
"1"
"8"
"Disney (Chase)"
"credit"
"0.000"
"5000.00"
"0.00"
"false"
#9
"9"
"1"
"9"
"Mortgage"
"loan"
"6.500"
"0.00"
"0.00"
"false"
#10
"10"
"1"
"10"
"Money Market"
"savings"
"0.000"
"0.00"
"0.00"
"false"
#11
"11"
"1"
"11"
"Student Loan"
"loan"
"5.125"
"0.00"
"0.00"
"false"
#12
"17"
"1"
"17"
"Lanore Mortgage"
"loan"
"5.250"
"0.00"
"0.00"
"false"
#13
"20"
"1"
"20"
"Stocks"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#14
"21"
"1"
"21"
"Goulka loan"
"loan"
"0.000"
"0.00"
"0.00"
"true"
#15
"22"
"1"
"22"
"McHenry Mortgage"
"loan"
"3.875"
"0.00"
"0.00"
"false"
#16
"23"
"1"
"23"
"Lanore Checking"
"checking"
"0.000"
"0.00"
"0.00"
"false"
#17
"24"
"1"
"24"
"Chase Freedom"
"credit"
"0.000"
"18000.00"
"0.00"
"true"
#18
"29"
"1"
"29"
"cash"
""
"0.000"
"0.00"
"0.00"
"true"
#19
"31"
"1"
"31"
"Chase Unlimited"
"credit"
"0.000"
"10400.00"
"0.00"
"true"
#20
"32"
"1"
"32"
"gift cards"
""
"0.000"
"0.00"
"0.00"
"true"
#21
"35"
"1"
"35"
"Investments"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#22
"13"
"1"
"7"
"CWGIX"
"investment"
"0.000"
"0.00"
"0.00"
"false"
#23
"14"
"1"
"7"
"ANCFX"
"investment"
"0.000"
"0.00"
"0.00"
"false"
#24
"15"
"1"
"7"
"AEPGX"
"investment"
"0.000"
"0.00"
"0.00"
"false"
#25
"16"
"1"
"7"
"ANWPX"
"investment"
"0.000"
"0.00"
"0.00"
"false"
#26
"18"
"1"
"20"
"DIS"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#27
"19"
"1"
"20"
"MSFT"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#28
"25"
"1"
"6"
"JDK"
"savings"
"0.000"
"0.00"
"0.00"
"false"
#29
"26"
"1"
"6"
"JPK"
"savings"
"0.000"
"0.00"
"0.00"
"false"
#30
"27"
"1"
"6"
"MJK"
"savings"
"0.000"
"0.00"
"0.00"
"false"
#31
"28"
"1"
"6"
"PFK"
"savings"
"0.000"
"0.00"
"0.00"
"false"
#32
"30"
"1"
"6"
"MJK Card"
"savings"
"0.000"
"0.00"
"0.00"
"false"
#33
"33"
"1"
"35"
"rIRA(Liza)"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#34
"34"
"1"
"35"
"rIRA(Ben)"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#35
"36"
"1"
"35"
"rIRA(JDK)"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#36
"37"
"1"
"35"
"rIRA(MJK)"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#37
"38"
"1"
"35"
"rIRA(JPK)"
"investment"
"0.000"
"0.00"
"0.00"
"true"
#38
"39"
"1"
"35"
"JointWROS"
"investment"
"0.000"
"0.00"
"0.00"
"true"
- add transactions (from last year)
→$data array(10)
trans_id
trans_time
debit_acct_id
credit_acct_id
cat_id
to_from
amount
shares
auto
note
check_num
debit_statement
credit_statement
#1
"21021"
"2025-12-07"
"6"
NULL
"120"
"Txfr to JP - SP reffing"
"60.00"
NULL
"false"
"JP checking"
NULL
NULL
NULL
#2
"21020"
"2025-12-07"
NULL
"6"
"111"
"SoccerPlanet"
"60.00"
NULL
"false"
"JP reffing"
NULL
NULL
NULL
#3
"21018"
"2025-12-07"
NULL
"6"
"111"
"SoccerPlanet"
"60.00"
NULL
"false"
"BJK reffing"
NULL
NULL
NULL
#4
"21019"
"2025-12-07"
NULL
"6"
"111"
"SoccerPlanet"
"40.00"
NULL
"false"
"BJK reffing"
NULL
NULL
NULL
#5
"20977"
"2025-12-05"
"6"
NULL
"29"
"U-C Sanitary District"
"89.28"
NULL
"true"
NULL
NULL
NULL
NULL
#6
"21017"
"2025-12-05"
"31"
NULL
"48"
"Farm & Fleet"
"34.87"
NULL
"false"
"garage door clicker"
NULL
NULL
NULL
#7
"21015"
"2025-12-03"
NULL
"6"
"140"
"Delta Dental"
"201.00"
NULL
"false"
NULL
NULL
NULL
NULL
#8
"21016"
"2025-12-03"
NULL
"6"
"140"
"Delta Dental"
"211.00"
NULL
"false"
NULL
NULL
NULL
NULL
#9
"21014"
"2025-12-02"
"31"
NULL
"48"
"Meijer"
"45.58"
NULL
"false"
NULL
NULL
NULL
NULL
#10
"21012"
"2025-11-28"
"4"
NULL
"80"
"Amazon.com"
"32.70"
NULL
"false"
NULL
NULL
NULL
NULL
- add transaction parts (for imported transactions)
→$data array(10)
part_id
trans_id
amount
cat_id
#1
"1"
"1479"
"22.76"
"91"
#2
"2"
"1479"
"13.86"
"30"
#3
"3"
"1674"
"54.43"
"91"
#4
"4"
"1635"
"5.05"
"41"
#5
"5"
"615"
"20.99"
"1"
#6
"6"
"615"
"6.87"
"48"
#7
"7"
"615"
"11.61"
"91"
#8
"8"
"1339"
"24.89"
"48"
#9
"9"
"870"
"25.00"
"30"
#10
"10"
"969"
"10.90"
"91"
- add balance info
- after final import, delete import columns... eventually