Asset Allocations

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Asset Allocations

GnuCash - User mailing list
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 (cash, stock, bonds, 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.

I've got some code that gets a balance out for a cash account...
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Asset Allocations

GnuCash - User mailing list
(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
advice, or have some code it would much appreciated,

best regards,

David


On Sun, Aug 21, 2016 at 10:09 PM, David Boyce <[hidden email]>
wrote:

> 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 (cash, stock, bonds, 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.
>
> I've got some code that gets a balance out for a cash account...
>
>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Asset Allocations

John Ralls-2

> On Aug 21, 2016, at 2:13 PM, David Boyce via gnucash-user <[hidden email]> 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



_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Asset Allocations

John Ralls-2

> On Aug 22, 2016, at 2:43 AM, David Boyce <[hidden email]> wrote:
>
> Thanks John, much appreciated, I'll have a tinker this week.  My aim is to be able to produce a report so I can see where assets are allocated, and be able to rebalance if required.  At the moment it's a manual job, but if I can hook Excel up to the GNUCash database using VBA, I'll be able to refresh the data at the touch of a button!
>

You're welcome, but please remember to copy the list on all replies.

Regards,
John Ralls



_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Asset Allocations

GnuCash - User mailing list
In reply to this post by John Ralls-2
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.
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Asset Allocations

Wm...
In reply to this post by GnuCash - User mailing list
On Sun, 21 Aug 2016 22:13:08 +0100, in gmane.comp.gnome.apps.gnucash.user,
David Boyce via gnucash-user <[hidden email]> 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.

search the web for "site:lists.gnucash.org sql balance sheet" will give you
a starter, you just want the assets but it easy to exclude stuff you don't
need.

> 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).

Although the SQL approach is undoubtedly interesting and certainly worth
doing to get an understanding of the sometimes mysterious way gnc stores
data (as John says, it is not normalised) I'm not sure SQL is what I'd end
up doing as a solution.

Try this

Reports / Assets & Liabilities / Asset piechart as it the closest to what
you want

Report options (play with these and everything else but I suggest, to
start)

General / Price source, you probably want nearest in time
General / Date, choose something meaningful, I usually start balance sheet
report development from Today because I have an idea in my head, may be End
of Prev Month or something else for you

Display / Show long a/c names, tick (NB for sort and slice in the SS)
Display / Show totals, tick (the main bit of data)
Display / Show percentages, untick (what the SS is for)
Display / Slices, probably max

Accounts / Level, probably all, see how it looks

Remember to Save Report Config with a useful name and keep doing this as
you refine.

Highlight, copy and paste the table bit on the RHS of the graph into your
SS of choice and do your stuff

e.g. you know Mutual Fund ABC is 20% stocks, 30% cash and 50% bonds, do the
split in the SS, *much* easier doing the work in the SS than changing a
query as the asset manager changes allocation, promise

To be clear, I think it is generally better for all if more people
understand how gnc uses SQL as that is the long term store, I just don't
think I'd use SQL for this particular task.

--
Wm

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Asset Allocations

Wm...
In reply to this post by GnuCash - User mailing list
On Mon, 22 Aug 2016 17:08:10 +0100, in gmane.comp.gnome.apps.gnucash.user,
David Boyce via gnucash-user <[hidden email]> 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

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Asset Allocations

GnuCash - User mailing list
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: <[hidden email]>]
>
> On Mon, 22 Aug 2016 17:08:10 +0100, in gmane.comp.gnome.apps.gnucash.user,
> David Boyce via gnucash-user <[hidden email]> 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.
>

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.