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