Account Charts – Show graphs for all linked and custom accounts, ordered by accounts with the highest value first.
TIP: you can control whether a specific account displays by going to the My Accounts page and changing the Display setting.
Schwab Main
❓
SELECT
p.id,p.user,IFNULL(i.acct_nick_name,p.acct_name) "acct",p.sym,p.qty,p.cost_per_share
,IFNULL(s.category1,'Uncategorized') "category1"
,IFNULL(s.category2,'Uncategorized') "category2"
,IFNULL(s.category3,'Uncategorized') "category3"
,IF(p.sym='$$$','Cash',IFNULL(s2.sector,'Uncategorized')) "sector"
,s2.sym_type
#,if(p.sym='$$$' OR inst_id = 'custom',1,s.price) "price"
,p.price "price"
,qty*cost_per_share "cost"
#,p.qty * if(p.sym='$$$' OR inst_id = 'custom',1,s.price) "market_value"
#,p.value "market_value"
,(p.value * 1) "market_value"
FROM msd_positions p
#JOIN msd_acct_info i ON (p.user = i.user AND ifnull(p.token_id,'custom') = ifnull(i.token_id,'custom') AND ifnull(p.mask,'mask') = ifnull(i.mask,'mask'))
#LEFT JOIN msd_acct_cats s ON (s.user = p.user AND s.acct_name = p.acct_name AND s.sym = p.sym)
LEFT JOIN msd_acct_info i ON (i.id = p.msd_acct_id)
LEFT JOIN msd_acct_cats s ON (s.msd_acct_id = p.msd_acct_id AND s.sym = p.sym)
LEFT JOIN msd_syms s2 ON (s2.user = p.user AND s2.sym = p.sym)
#LEFT JOIN stocks_all s ON (s.sym = p.sym)
# grab the latest date only
JOIN (SELECT USER,MAX(txn_date) "txn_date" FROM msd_positions GROUP BY USER) t ON (t.user = p.user AND t.txn_date = p.txn_date)
WHERE p.user = 'kRLS4aSli2oug5rpwcaa2Q=='
AND p.acct_name = 'UvmylY0VEgph+fa17HwqWg==' AND IFNULL(p.mask,0) = if('6319'='',0,'6319')
AND IFNULL(i.display,1) = 1
ORDER BY p.value DESC❓
SELECT date_format(txn_date,'%Y-%m-%d') "txn_date"
,t.sym "dim"
,SUM(value) "metric"
,SUM(price) "price"
,SUM(qty) "qty"
#,SUM(SUM(value)) "metric"
FROM msd_positions t
JOIN msd_acct_info i ON (i.id = t.msd_acct_id)
LEFT JOIN msd_syms s2 ON (s2.user = t.user AND s2.sym = t.sym)
WHERE 1=1
AND t.user='kRLS4aSli2oug5rpwcaa2Q==' AND t.acct_name = 'UvmylY0VEgph+fa17HwqWg==' AND IFNULL(t.mask,0) = if('6319'='',0,'6319') AND i.display = 1
GROUP BY date_format(txn_date,'%Y-%m-%d')
,t.sym
ORDER BY 2,1 Wife 401k
❓
SELECT
p.id,p.user,IFNULL(i.acct_nick_name,p.acct_name) "acct",p.sym,p.qty,p.cost_per_share
,IFNULL(s.category1,'Uncategorized') "category1"
,IFNULL(s.category2,'Uncategorized') "category2"
,IFNULL(s.category3,'Uncategorized') "category3"
,IF(p.sym='$$$','Cash',IFNULL(s2.sector,'Uncategorized')) "sector"
,s2.sym_type
#,if(p.sym='$$$' OR inst_id = 'custom',1,s.price) "price"
,p.price "price"
,qty*cost_per_share "cost"
#,p.qty * if(p.sym='$$$' OR inst_id = 'custom',1,s.price) "market_value"
#,p.value "market_value"
,(p.value * 1) "market_value"
FROM msd_positions p
#JOIN msd_acct_info i ON (p.user = i.user AND ifnull(p.token_id,'custom') = ifnull(i.token_id,'custom') AND ifnull(p.mask,'mask') = ifnull(i.mask,'mask'))
#LEFT JOIN msd_acct_cats s ON (s.user = p.user AND s.acct_name = p.acct_name AND s.sym = p.sym)
LEFT JOIN msd_acct_info i ON (i.id = p.msd_acct_id)
LEFT JOIN msd_acct_cats s ON (s.msd_acct_id = p.msd_acct_id AND s.sym = p.sym)
LEFT JOIN msd_syms s2 ON (s2.user = p.user AND s2.sym = p.sym)
#LEFT JOIN stocks_all s ON (s.sym = p.sym)
# grab the latest date only
JOIN (SELECT USER,MAX(txn_date) "txn_date" FROM msd_positions GROUP BY USER) t ON (t.user = p.user AND t.txn_date = p.txn_date)
WHERE p.user = 'kRLS4aSli2oug5rpwcaa2Q=='
AND p.acct_name = 'MwhaVjiju0KWlDEcpqnNKA==' AND IFNULL(p.mask,0) = if('2'='',0,'2')
AND IFNULL(i.display,1) = 1
ORDER BY p.value DESC❓
SELECT date_format(txn_date,'%Y-%m-%d') "txn_date"
,t.sym "dim"
,SUM(value) "metric"
,SUM(price) "price"
,SUM(qty) "qty"
#,SUM(SUM(value)) "metric"
FROM msd_positions t
JOIN msd_acct_info i ON (i.id = t.msd_acct_id)
LEFT JOIN msd_syms s2 ON (s2.user = t.user AND s2.sym = t.sym)
WHERE 1=1
AND t.user='kRLS4aSli2oug5rpwcaa2Q==' AND t.acct_name = 'MwhaVjiju0KWlDEcpqnNKA==' AND IFNULL(t.mask,0) = if('2'='',0,'2') AND i.display = 1
GROUP BY date_format(txn_date,'%Y-%m-%d')
,t.sym
ORDER BY 2,1