Asset Allocations

[Wm]

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.

-- 
Wm