Asset Allocations

[John Ralls]

> On Aug 21, 2016, at 2:13 PM, David Boyce via gnucash-user <gnucash-user at gnucash.org> wrote:
> 
> (apologies hit send by accident rather than paste!)
> 
> I was wondering if anyone could offer me any advice on getting a stock
> valuation out of GNUCash via SQL.
> 
> I'm looking to create a spreadsheet in Excel that gets data out of the
> GNUCash database.  I'm looking to have a spreadsheet in excel that sets
> each account type according to my own classification (cash, stock, bonds,
> gold, fixed asset) etc and then be able to produce a report that says I
> have X percentage saved as bonds etc.  I know perhaps I should do this
> within GNUCash but the problem is some funds are weighted X across several
> asset classes so I need to allocate them when producing the report).
> 
> I've got some code that gets a balance out for a cash account...
> 
>  Sql = "        SELECT "
>  Sql = Sql & "    SUM(s.value_num / s.value_denom) as 'Value' "
>  Sql = Sql & "  FROM "
>  Sql = Sql & "    transactions As t "
>  Sql = Sql & "    inner Join "
>  Sql = Sql & "    splits As s "
>  Sql = Sql & "    inner Join "
>  Sql = Sql & "    accounts as a ON t.guid = s.tx_guid "
>  Sql = Sql & "    && s.account_guid = a.guid "
>  Sql = Sql & "  WHERE "
>  Sql = Sql & "    a.guid = '" & thisGuid & "' AND "
>  Sql = Sql & "    a.guid = '" & thisGuid & "' AND t.post_date <=
> CURRENT_DATE() "
> 
> but for stock valuations I'm guessing I'll need something that takes each
> buy/sell into consideration and the price.  If anyone would have any

The simplest way would be to use the prices table, joining on account.commodity_guid = prices.commodity_guid. If you the assets you hold are priced in multiple currencies you'll have the added complication of converting to a common one for your comparisons; note the price.currency_guid for that purpose.

The next wrinkle is price-date and whether you're trying to balance your portfolio on book value (the price at which you purchased each asset) or current market value. The latter is the more common, and for that you'll want to keep your prices table up-to-date with Finance::Quote and select the price with the latest prices.date. If you want to track book value then you'll probably want to make use of the lots facility as well to help you figure out which splits to price; with that part out of the way you can join your buy splits on split.date = prices.date as long as there's a price matching each buy transaction (IIRC that was guaranteed with 2.6.0, so if you have splits created with an older version of GnuCash you'll need to create prices in the prices table for those splits. It will be easier to do that than to find the nearest-in-time matching price.)

Regards,
John Ralls