Accounts balance for any date

[vomikan]

http://forum.moneymanagerex.org/viewtopic.php?f=23&t=6952

[vomikan]

@aprobinda could you create this report?

[asanfilov]

@vomikan original SQL code could be modified in the following way. I have tested on my accounts and it seems to show the right overview:
select a.ACCOUNTNAME, ( select a.INITIALBAL + total(t.TRANSAMOUNT) from (select ACCOUNTID, STATUS, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT ,TRANSDATE from CHECKINGACCOUNT_V1 union all select TOACCOUNTID, STATUS, TOTRANSAMOUNT ,TRANSDATE from CHECKINGACCOUNT_V1 where TRANSCODE = 'Transfer' ) as t where t.ACCOUNTID = a.ACCOUNTID and t.TRANSDATE <= '2017-02-25' and t.STATUS <> 'V' ) as Balance from ACCOUNTLIST_V1 as a where a.STATUS = 'Open' group by a.ACCOUNTNAME order by a.ACCOUNTNAME asc;

[vomikan]

My SQL script is (based on your but with more readable structure):

with b as  (
	    select ACCOUNTID, STATUS
            , (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
            ,TRANSDATE 
            from CHECKINGACCOUNT_V1 
            union all 
            select TOACCOUNTID, STATUS, TOTRANSAMOUNT ,TRANSDATE 
            from CHECKINGACCOUNT_V1 
            where TRANSCODE = 'Transfer' 
   ) 
select a.ACCOUNTNAME, 
total(TRANSAMOUNT) + a.INITIALBAL as Balance 
from ACCOUNTLIST_V1 as a, b
where a.STATUS = 'Open' 
    and b.ACCOUNTID = a.ACCOUNTID 
    and b.STATUS <> 'V' 
    and b.TRANSDATE <= '2017-02-25' 
group by a.ACCOUNTNAME order by a.ACCOUNTNAME asc;

[vomikan]

There is already issued the report 'Account Summary'
I'll modified it regarding this issue.

Seems it would be nice option to add a date option.

[vomikan]

Only for mmex >= 1.4.0
Account summary.zip

Use nightly biild for tests

[vomikan]

[vomikan]

@petubl
Possible SQL for new DB structure:

with t as
 (select ACCOUNTID,
         STATUS,
         TRANSDATE,
         (case
           when TRANSCODE = 'Deposit' then
            TRANSAMOUNT
           else
            -TRANSAMOUNT
         end) as TRANSAMOUNT
    from CHECKINGACCOUNT
   where STATUS NOT LIKE 'V%'
  union all
  select TOACCOUNTID, STATUS, TRANSDATE, TOTRANSAMOUNT
    from CHECKINGACCOUNT
   where TRANSCODE = 'Transfer'
     and STATUS NOT LIKE '_V'),
c as
 (with h as
     (select CURRENCYID, max(CURRVALUE) CURRVALUE
       from CURRENCYHISTORY
      where CURRDATE <= '&single_date'
      group by CURRENCYID)
    select c.CURRENCYID,
           c.PFX_SYMBOL,
           c.SFX_SYMBOL,
           ifnull(h.CURRVALUE, 1) BASECONVRATE
      from CURRENCYFORMATS c
      left join h
        on h.CURRENCYID = c.CURRENCYID)
select a.ACCOUNTNAME,
       c.PFX_SYMBOL,
       c.SFX_SYMBOL,
       c.BASECONVRATE,
       total(t.TRANSAMOUNT) + a.INITIALBAL as Balance
  from ACCOUNTLIST as a, t, c
 where a.ACCOUNTTYPE not in ('Investment')
   and c.CURRENCYID = a.CURRENCYID
   and a.STATUS = 'Open'
   and a.ACCOUNTID = t.ACCOUNTID
   and t.TRANSDATE <= '&single_date'
 group by a.ACCOUNTNAME
 order by a.ACCOUNTNAME asc;

[petubl]

Thank you very much @vomikan the sql code works now - i still get many decimal points on the 1st, 3rd, 5th, 7th etc lines on the report.

Any idea why this happens?

EDIT

I had only checked the sql test output.

The actual report renders beautifully! Thank you @vomikan

[vomikan]

@petubl I've just updated SQL code, please, update

[vomikan]

Most important changes:

• STATUS NOT LIKE '%V' -- now transfer transaction has XX (2 bytes) status code.
• from CURRENCYHISTORY -- now there is no BASECONVRATE value in CURRENCYFORMATS table. In that case convertion rate taken as max value for choosen date. In case if value missing it became =1.

[petubl]

Works great! Thanks

[vomikan]

I've created wet (draft) report with other graph generator.
Account.summary_10.zip

It more complicated but looks better. But needed a lot of fixes.

[petubl]

The new graph looks very good! I prefer the multicolored list from the original report.

Thanks for your time in doing this!

[petubl]

@vomikan
I spent some time playing around with the new report and have the following to report:

In the chart, the largest four accounts are represented by a different colour (orange, green, blue, purple). However, the remaining accounts are displayed in the chart using the same colour as the one used in the case of the fourth one. So accounts 4, 5, 6, 7 etc are the same colour, purple - or maybe they are different shades of purple (those accounts have much lower balances and their share of the pie is quite small so i am not sure if there is a shade difference).

In the table of the previous version of the report, you used a different colour for each total on the column on the right, labelled "Base Currency". I think it made the report more pleasing to the eye, but is not included in the new version.

It would be great if the colours were brought back to the last column of the report, and then the same colours were used in the chart - I don't know if its something that is easily doable, and which you might be inclined to do...

Thank you once again for your time in looking into this.

[vomikan]

https://github.com/moneymanagerex/general-reports/tree/master/packages/Account/AccountBalances