Asset Allocations

[Wm]

On Sun, 21 Aug 2016 22:13:08 +0100, in gmane.comp.gnome.apps.gnucash.user,
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.

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