Report for asset distribution among securities

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

Report for asset distribution among securities

Daniel Kraft-4
Hi!

I'm using Gnucash for private accounting (surprise!) but also to keep
track of investments in various securities.  I think that it would be
great to create a report that displays my portfolio as distribution of
funds over these securities (could be as a piechart or table).

I. e., I would like to get something like this:

  25% EUR
  25% USD
  20% XAU
  20% Fund ABC
  10% Stock XYZ

The report "Asset Piechart" is already quite close to this -- but it
groups things together by the account structure and not by security.  Of
course, I could get the effect I want if I structure my accounts by
security.  That is, however, not what I want.  I may have multiple
EUR/USD accounts or hold the same other security in multiple broker
accounts, for instance.  In that case, I prefer to group the accounts by
bank/broker instead of by security.

Is there something I can do to get a report as I want it, grouped by
security that I missed?

Otherwise, since I know a bit of Scheme/Guile, I guess that I can write
my own script to create it.  In that case, do you think there could be
interest to publish it with Gnucash as a new report type?

Yours,
Daniel

--
http://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
Done:  Arc-Bar-Cav-Hea-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Mon-Pri


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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: Report for asset distribution among securities

David Carlson-4

   
There are already several reports that either deal only with investment s or can be customized.
Exactly what are you looking for? 
David C


Sent via the Samsung Galaxy S® 5 ACTIVE™, an AT&T 4G LTE smartphone

-------- Original message --------
From: Daniel Kraft <[hidden email]>
Date: 08/30/2015  9:51 AM  (GMT-06:00)
To: Gnucash User List <[hidden email]>
Subject: Report for asset distribution among securities

_______________________________________________
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.
_______________________________________________
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: Report for asset distribution among securities

Daniel Kraft-4
Hi!

On 2015-08-30 18:39, david.carlson.417 wrote:
> There are already several reports that either deal only with investment
> s or can be customized.
>
> Exactly what are you looking for?

I tried to describe the situation in my original email.  Is it unclear?
 I want a report just like the "Asset Piechart", except that it groups
funds by security instead of accounts.

As an example, consider the following account hierarchy:

Assets
  Bank A
    Saving account (EUR)
    Security depot
      Fund ABC
      Stock XYZ
      Gold ETF
  Bank B
    Checking account (EUR)
  Brokerage account
    Clearing account (USD)
    Security depot
      Stock XYZ
  Physical XAU

Using the "Asset Piechart", I can (as far as I know) only get something
like the following:

  *) Distribution of assets between Bank A, Bank B, Brokerage account
and Physical XAU, or
  *) Distribution of assets in, say, the security deposit of Bank A, or
  *) something like this for the other subaccounts

But I want to combine *all* assets in all subaccounts for a given
security.  I. e., the chart should show the distribution of funds between:

  *) EUR: Bank A + Bank B
  *) USD: Brokerage account
  *) ABC: Bank A
  *) XYZ: Bank A + Brokerage account
  *) Gold: Bank A (ETF) + physical

(Note that I'm not sure if the example makes full sense from an
investment perspective.  It is not my real situation, but I hope to make
it clear what I mean.)

Yours,
Daniel

--
http://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
Done:  Arc-Bar-Cav-Hea-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Mon-Pri


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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Report for asset distribution among securities

David Carlson-4
On 8/30/2015 12:02 PM, Daniel Kraft wrote:

> Hi!
>
> On 2015-08-30 18:39, david.carlson.417 wrote:
>> There are already several reports that either deal only with investment
>> s or can be customized.
>>
>> Exactly what are you looking for?
> I tried to describe the situation in my original email.  Is it unclear?
>  I want a report just like the "Asset Piechart", except that it groups
> funds by security instead of accounts.
>
> As an example, consider the following account hierarchy:
>
> Assets
>   Bank A
>     Saving account (EUR)
>     Security depot
>       Fund ABC
>       Stock XYZ
>       Gold ETF
>   Bank B
>     Checking account (EUR)
>   Brokerage account
>     Clearing account (USD)
>     Security depot
>       Stock XYZ
>   Physical XAU
>
> Using the "Asset Piechart", I can (as far as I know) only get something
> like the following:
>
>   *) Distribution of assets between Bank A, Bank B, Brokerage account
> and Physical XAU, or
>   *) Distribution of assets in, say, the security deposit of Bank A, or
>   *) something like this for the other subaccounts
>
> But I want to combine *all* assets in all subaccounts for a given
> security.  I. e., the chart should show the distribution of funds between:
>
>   *) EUR: Bank A + Bank B
>   *) USD: Brokerage account
>   *) ABC: Bank A
>   *) XYZ: Bank A + Brokerage account
>   *) Gold: Bank A (ETF) + physical
>
> (Note that I'm not sure if the example makes full sense from an
> investment perspective.  It is not my real situation, but I hope to make
> it clear what I mean.)
>
> Yours,
> Daniel
>

I did not see any of that detail on my (not-so)smartphone, only one line.
I think that cannot be done with the existing reports as you have
found.  Perhaps others have suggestions to help you.

David C
_______________________________________________
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: Report for asset distribution among securities

Daniel Kraft-4
Hi!

On 2015-08-30 20:26, David Carlson wrote:
> I did not see any of that detail on my (not-so)smartphone, only one line.
> I think that cannot be done with the existing reports as you have
> found.  Perhaps others have suggestions to help you.

Thank you for the clarification!  Just for fun, I've started to work on
implementing it as a custom report myself.  In case it turns out that it
is actually already possible, it will be a useful learning experience
(and I'm definitely still interested to hear about that solution, if it
exists!).  Otherwise, I will get the report hopefully by myself and may
be able to share it.

Yours,
Daniel

--
http://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
Done:  Arc-Bar-Cav-Hea-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Mon-Pri


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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Report for asset distribution among securities

Wm...
Sun, 30 Aug 2015 20:43:05 <[hidden email]>
Daniel Kraft <[hidden email]> wrote...

>On 2015-08-30 20:26, David Carlson wrote:
>> I did not see any of that detail on my (not-so)smartphone, only one line.
>> I think that cannot be done with the existing reports as you have
>> found.  Perhaps others have suggestions to help you.
>
>Thank you for the clarification!  Just for fun, I've started to work on
>implementing it as a custom report myself.  In case it turns out that it
>is actually already possible, it will be a useful learning experience
>(and I'm definitely still interested to hear about that solution, if it
>exists!).  Otherwise, I will get the report hopefully by myself and may
>be able to share it.

if you can do Scheme SQL should be easy

===
-- this is a balance sheet of sorts
-- so we need info at a point in time
--
-- get a date for each commodity
-- play with this if you want another point in time
-- I've chosen the latest date presuming that equates
-- to most recent unless you are prescient, in which case please
-- send the gnc devs all your money, they need it :)
CREATE
        OR REPLACE VIEW wrk_asset_dist_prices_A AS

SELECT commodities.mnemonic AS commodity_mnemonic
        ,commodities.guid AS commodity_guid
        ,max(DATE) AS DATE
FROM prices
        ,commodities
WHERE prices.commodity_guid = commodities.guid
GROUP BY commodities.mnemonic
        ,commodities.guid;

-- get the price for each commodity using the date above
CREATE
        OR REPLACE VIEW wrk_asset_dist_prices_B AS

SELECT DISTINCT prices.value_num AS price
        ,commodities.mnemonic
        ,prices.DATE
FROM prices
        ,wrk_asset_dist_prices_A
        ,commodities
WHERE prices.DATE = wrk_asset_dist_prices_A.DATE
        AND prices.commodity_guid =
wrk_asset_dist_prices_A.commodity_guid
        AND wrk_asset_dist_prices_A.commodity_guid = commodities.guid
        --      ORDER BY commodities.mnemonic ,price
        -- GROUP BY commodities.mnemonic ,price
        ;

-- get the number (not value) of each commodity
CREATE
        OR REPLACE VIEW wrk_asset_dist_quantities AS

SELECT sum(splits.quantity_num) AS quantity
        ,commodities.mnemonic
FROM splits
JOIN accounts ON splits.account_guid = accounts.guid
JOIN commodities ON accounts.commodity_guid = commodities.guid
WHERE accounts.account_type NOT IN (
                'EXPENSE'
                ,'INCOME'
                )
GROUP BY commodities.mnemonic
ORDER BY commodities.mnemonic;

-- glue the number and price together
SELECT quantity * price AS value
        ,Q.mnemonic
FROM wrk_asset_dist_quantities AS Q
        ,wrk_asset_dist_prices_B AS P
WHERE Q.mnemonic = P.mnemonic;
===

you should end up with a table something like
===
3630000000000;"AAL.L"
373120000000000;"ATST.L"
54943160000000;"EUR"
59313800000000;"USD"
5000000;"XXX"
===
which you can copy and paste into LibreOffice Calc or similar for
convenient graphing.

even if your USD or EUR are split between multiple gnc accounts.


Notes:

1) tested using postgres, should work with little change using SQLite3,
MySQL is, as usual, exercise for reader

2) I've left out denominations to make things clear, promise!  They are
easy to add in afterwards.

3) the views aren't necessary, I've left them in to (hopefully) explain
that getting the date and price and quantity of stuff is significant
before you go making pretty pictures.

4) this looks like a useful thing, if people other than the OP are
interested I'll tidy it up.

--
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: Report for asset distribution among securities

Daniel Kraft-4
Hi!

On 2015-08-31 22:55, Wm... wrote:

> Sun, 30 Aug 2015 20:43:05 <[hidden email]>
> Daniel Kraft <[hidden email]> wrote...
>
>> On 2015-08-30 20:26, David Carlson wrote:
>>> I did not see any of that detail on my (not-so)smartphone, only one line.
>>> I think that cannot be done with the existing reports as you have
>>> found.  Perhaps others have suggestions to help you.
>>
>> Thank you for the clarification!  Just for fun, I've started to work on
>> implementing it as a custom report myself.  In case it turns out that it
>> is actually already possible, it will be a useful learning experience
>> (and I'm definitely still interested to hear about that solution, if it
>> exists!).  Otherwise, I will get the report hopefully by myself and may
>> be able to share it.
>
> if you can do Scheme SQL should be easy
> ...
Thanks for the SQL stuff!  This looks, indeed, useful for export of data
and later analysis with other means (e. g., LibreOffice as you mention it).

My own route, however, was to implement a custom report in Scheme.  This
makes accessing the report quick and easy, and was an interesting
refreshment of programming Scheme for me.  (As well as some Gnucash
concepts.)

It works already, but is not yet cleaned up.  I plan to submit a pull
request against Gnucash with my patch once it is ready.  I think that
this approach complements your export SQL nicely for different usage
patterns.

Yours,
Daniel

--
http://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
Done:  Arc-Bar-Cav-Hea-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Mon-Pri


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

signature.asc (853 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Report for asset distribution among securities

Wm...
Tue, 1 Sep 2015 12:27:29 <[hidden email]>
Daniel Kraft <[hidden email]> wrote...

>Hi!
>
>On 2015-08-31 22:55, Wm... wrote:
>> Sun, 30 Aug 2015 20:43:05 <[hidden email]>
>> Daniel Kraft <[hidden email]> wrote...
>>
>>> On 2015-08-30 20:26, David Carlson wrote:
>>>> I did not see any of that detail on my (not-so)smartphone, only one line.
>>>> I think that cannot be done with the existing reports as you have
>>>> found.  Perhaps others have suggestions to help you.
>>>
>>> Thank you for the clarification!  Just for fun, I've started to work on
>>> implementing it as a custom report myself.  In case it turns out that it
>>> is actually already possible, it will be a useful learning experience
>>> (and I'm definitely still interested to hear about that solution, if it
>>> exists!).  Otherwise, I will get the report hopefully by myself and may
>>> be able to share it.
>>
>> if you can do Scheme SQL should be easy
>> ...
>
>Thanks for the SQL stuff!  This looks, indeed, useful for export of data
>and later analysis with other means (e. g., LibreOffice as you mention it).
>
>My own route, however, was to implement a custom report in Scheme.  This
>makes accessing the report quick and easy, and was an interesting
>refreshment of programming Scheme for me.  (As well as some Gnucash
>concepts.)
>
>It works already, but is not yet cleaned up.  I plan to submit a pull
>request against Gnucash with my patch once it is ready.  I think that
>this approach complements your export SQL nicely for different usage
>patterns.

Have you made your report tidy so that you may share it, Daniel ?  It
would be interesting for me to compare similar reports in disparate
formats and possibly educational in a broader sense for all.

--
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: Report for asset distribution among securities

Daniel Kraft
Hi!

On 2015-10-02 00:27, Wm... wrote:
> Have you made your report tidy so that you may share it, Daniel ?  It
> would be interesting for me to compare similar reports in disparate
> formats and possibly educational in a broader sense for all.

Yes, I submitted a patch that adds this "security piechart" to the
reports included in Gnucash.  It was merged by the developers and will
be included in the next version.

If you do not want to run the latest version (which I don't do either,
I'm using the one in Debian Jessie), you can use the attached adapted file.

Place it in your ~/.gnucash folder and add the following line to
~/.gnucash/config.user:

  (load (gnc-build-dotgnucash-path "securities-chart.scm"))

In case you use an OS different from GNU/Linux, you have to look for a
different folder, I guess.  I don't know which one.

Hope that helps!  Yours,
Daniel

--
http://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
Done:  Arc-Bar-Cav-Hea-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Mon-Pri

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

securities-chart.scm (28K) Download Attachment
signature.asc (836 bytes) Download Attachment