Asset Allocations

[David Boyce]

Hi Wm,

Thanks for the suggestions, I did originally try coping and pasting the 
data from the GNUCash Balance Sheet report into Excel, but I found 
problems with the formatting because I have assets tiered at different 
levels and so the worksheet needed lots of work after pasting.

The SQL approach from Excel (accessing my GNUCash data stored in mysql) 
seems to be working quite well, and can be updated in less than a second 
at the click of a button in Excel.  I've got one worksheet where I have 
a list of all my asset account ids, the name, what asset group it 
belongs to eg (stocks, bonds, cash etc).  If an asset is split (eg 80% 
stocks, 20% bonds) then there's 2 entries and a percentage allocation 
for each.  I don't often add accounts so it should be easily maintained.

I've got a master worksheet with 2 buttons.  1 button refreshes the 
data, and gives me a current summary of where assets are allocated. I've 
got target values too, and it highlights if an asset class has moved > 
X% out of range.  A second button stores the values for that day for 
historical purposes.

I agree with your sentiments that accessing the data using sql is error 
prone, and at some point will come unstuck.  But i guess I can fix it 
as/when that happens, and the total assets figure from the balance sheet 
report in GNUCash gives an immediate warning if my queries are failing 
to produce the correct results, and most assets groups I can reconcile 
directly, it's just where something is split like a fund which is made 
up of cash/bonds.

Thanks again,

David


On 28/08/16 22:50, Wm wrote:
> [FYI: This is a copy of a message posted to <news:gmane.comp.gnome.apps.gnucash.user> Message-ID: <1eo5zwfyuowsm.dlg at tcnw81.tarrcity.invalid>]
>
> On Mon, 22 Aug 2016 17:08:10 +0100, in gmane.comp.gnome.apps.gnucash.user,
> David Boyce via gnucash-user <gnucash-user at gnucash.org> wrote:
>
>> 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)...
> Some people (not me) make a living out of refining large scale SQL queries
> and it may even be considered a career choice :)  At our gnc level
> separate, defined queries that *you* understand are waaaay better than a
> consolidated one that you can't work out how to change next month.  Do
> stuff like this on your own terms!
>
>> 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"
> Comment: thisGuid will mean nothing to you next week, you may find yourself
> keeping a list of account guids in a spreadsheet, etc.  This is defeating
> the point, queries should add usefulness to data not subtract from it.  A
> recursive query is the answer but they aren't SQL 101.
>
> Search for "site:lists.gnucash.org sql CoA recursive" for some stuff to get
> you started.
>
> Also note that you may hold Anglo American in more than one fund.  If you
> are splitting out at that level you should concentrate as I suggested in my
> previous message on the SS side.
>
>> 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 "
> This is error prone.  I know (or think) you are doing it for yourself but
> my instinct says this will break soon.
>
> P.S. what backend are you using and how are you interacting with it?
> People that do SQL daily usually write their SQL "as is" rather than how
> you are presenting it.  IMO a good front end GUI is an essential nowadays
> at the initial stage of prototyping and playing with data.  Sure, I grew up
> without one but times change in case any older folk want to moan :)
>
>> 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.
> Theory is fine but the gnc db isn't normalised (repeating, I know).  For
> gnc an SQL db is still, at this point in time, a means of storage with
> curious access methods.  You should not presume consistency of data or any
> theoretical stuff.
>