Report for asset distribution among securities

[Wm...]

Sun, 30 Aug 2015 20:43:05 <55E34EB9.4070102 at domob.eu>
Daniel Kraft <d at domob.eu> 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...