Asset Allocations
David Boyce
boycey3 at googlemail.com
Mon Aug 22 12:08:10 EDT 2016
I haven't tested this thoroughly yet, but getting the stock value via a sql
query looks possible using the below (excuse my novice sql, I'd imagine
there's a way of doing this in one query)...
1. Get the number of shares held for a particular guid
Sql = " SELECT "
Sql = Sql & " SUM(quantity_num / quantity_denom) "
Sql = Sql & " FROM "
Sql = Sql & " splits a, transactions b "
Sql = Sql & " WHERE "
Sql = Sql & " account_guid = '" & thisGuid & "' AND "
Sql = Sql & " a.tx_guid = b.guid AND "
Sql = Sql & " b.post_date <= CURRENT_DATE"
2. Look up the commodity guid for this account guid
Sql = " SELECT "
Sql = Sql & " a.commodity_guid "
Sql = Sql & " FROM "
Sql = Sql & " accounts a "
Sql = Sql & " WHERE "
Sql = Sql & " a.guid = '" & thisGuid & "'"
3. Get the latest price for this commodity
Sql = " SELECT "
Sql = Sql & " value_num/value_denom "
Sql = Sql & " FROM "
Sql = Sql & " prices p "
Sql = Sql & " WHERE "
Sql = Sql & " p.commodity_guid = '" & commodityGuid & "' AND "
Sql = Sql & " p.date <= CURRENT_DATE "
Sql = Sql & " ORDER BY "
Sql = Sql & " p.date DESC "
Sql = Sql & " LIMIT 1 "
Should then just be a case of multiplying the number of stock held by the
commodity price. My commodities are all held in one currency.
More information about the gnucash-user
mailing list