Expenses vs Deposit comparison

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
aramacciotti
Senior MMEX User
Posts: 25
Joined: Mon Jun 02, 2014 7:17 am
Are you a spam bot?: No

Expenses vs Deposit comparison

Post by aramacciotti »

Happy to share my first General Report. I hope that someone will improve it so I can take advantage of that.

This report will graph the monthly values of Deposits and Expenses (Withdrawals) and report the same in a table under the graph itself.
Attachments
Expenses vs Deposits comparison.grm
(1.68 KiB) Downloaded 656 times
User avatar
Renato
MVP MMEX User
Posts: 655
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Expenses vs Deposit comparison

Post by Renato »

Hello aramacciotti

Super, thank you very much.

Had even a suggestion for improvement:
instead of

<td><TMPL_VAR "TotalDeposits"></td>
<td><TMPL_VAR "TotalExpenses"></td>
use

Code: Select all

       <td class = "money, text-right"><TMPL_VAR "TotalDeposits"></td>
       <td class = "money, text-right"><TMPL_VAR "TotalExpenses"></td>
and at the conclusion of the insert script

Code: Select all

<script>
<!--Format numbers-->
    function currency(n) { n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2); }
    var elements = document.getElementsByClassName("money, text-right");
    for (var i = 0; i < elements.length; i++)
        { elements[i].innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>"; }
</script>
and here is the result, but for me useless, because the shares with MMEX can not be performed as accounting.
MMEX6.jpg
(54.12 KiB) Downloaded 1684 times
Renato Forum Administrator
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Expenses vs Deposit comparison

Post by Nikolay »

super!!!
My improuvments:

Code: Select all

select strftime('%m', c.TRANSDATE) as MONTH
    ,strftime('%Y', c.TRANSDATE) as YEAR
    , total(case c.TRANSCODE when 'Deposit'  then TRANSAMOUNT * cf.BaseConvRate end) as TotalDeposits
    , total(case c.TRANSCODE when 'Withdrawal' then TRANSAMOUNT * cf.BaseConvRate end) as TotalExpenses
from CHECKINGACCOUNT_V1 c
    left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID = c.ACCOUNTID
    left join currencyformats_v1 cf on cf.currencyid=AC.currencyid
where TRANSDATE >= '2009-01-01'
and transcode != 'Transfer'
and c.status !='V'
and ac.status !='Closed'
group by year, month
order by year,month

Code: Select all

local deposit = '';
local depositdata = '';
local expense = '';
local expensedata = '';
local month = '';
local months = {['01']='Jan', ['02']='Feb', ['03']='Mar', ['04']='Apr', ['05']='May', ['06']='Jun', ['07']='Jul', ['08']='Aug', ['09']='Sep', ['10']='Oct', ['11']='Nov', ['12']='Dec'};
function handle_record(record)
    depositdata = depositdata .. record:get("TotalDeposits") .. ',';
    expensedata = expensedata .. record:get("TotalExpenses") .. ',';
    local entryDate = months[record:get("MONTH")] .. '-' .. record:get("YEAR");
    month = month .. '"' .. entryDate .. '",';
    record:set('ENTRY_DATE', entryDate)
end
function complete(result)
    deposit = [[
        {label:"Deposits",
        fillColor : "rgba(151,187,205,0.2)",
        strokeColor : "rgba(151,187,205,1)",
        pointColor : "rgba(151,187,205,1)",
        pointStrokeColor:"#fff",
        pointHighlightFill: "#fff",
        pointHighlightStroke: "rgba(151,187,205,1)",
        data : [%s]}
    ]]
    deposit = string.format(deposit, depositdata);
    expense = expense .. '{label:"Expenses",fillColor : "rgba(253,17,2,0.2)", strokeColor : "rgba(253,17,2,1)", pointColor : "rgba(253,17,2,1)",pointStrokeColor:"#fff",pointHighlightFill: "#fff",pointHighlightStroke: "rgba(253,17,2,1)", data : [';
    expense = expense .. string.sub(expensedata,1,-2) .. ']}';
    result:set('TREND_DEPOSITS', deposit);
    result:set('TREND_EXPENSES', expense);
    result:set('TREND_MONTHS', string.sub(month,1,-2));
end

Code: Select all

<!DOCTYPE html><h3><TMPL_VAR REPORTNAME></h3>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
 <title><TMPL_VAR REPORTNAME></title> 
<link href = "master.css" rel = "stylesheet" /> 
<script src = "Chart.js"></script> 
</head>
<body>
    <div class="container">
<TMPL_VAR TODAY><hr>

<canvas id="reportChart" width="1200" height="600"></canvas></td></tr>
<table class='table'>
    <thead>
    <tr>
        <th>Month/Year</th>
        <th>Total Deposit</th>
        <th>Total Expenses</th>
     </tr>
    </thead>
    <tbody>
    <TMPL_LOOP NAME=CONTENTS>
    <tr>
        <td><TMPL_VAR "ENTRY_DATE"></td>
        <td class='money'><TMPL_VAR "TotalDeposits"></td>
        <td class='money'><TMPL_VAR "TotalExpenses"></td>
        </tr>
    </TMPL_LOOP>
 </tbody>
</table>
<TMPL_LOOP ERRORS>
    <hr>
    <TMPL_VAR ERROR>
</TMPL_LOOP>
    </div>   
</body>
<script type="text/javascript">
    <!-- Chart -->
    var data = {
        labels : [<TMPL_VAR "TREND_MONTHS">],
        datasets : [<TMPL_VAR "TREND_DEPOSITS">,<TMPL_VAR "TREND_EXPENSES">]
    }
    var ctx = document.getElementById("reportChart").getContext("2d");
    var reportChart = new Chart(ctx).Line(data);
</script>

</html>
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Expenses vs Deposit comparison

Post by Nikolay »

the package 
Attachments
Expenses vs Deposits comparison.grm
(2.25 KiB) Downloaded 593 times
User avatar
Renato
MVP MMEX User
Posts: 655
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Expenses vs Deposit comparison

Post by Renato »

Hello Nikolay

Image

However, the total does not coincide with the other evaluations.
Desirable would be another column with the cumulative difference and possibly even with the state the assets.
Renato Forum Administrator
aramacciotti
Senior MMEX User
Posts: 25
Joined: Mon Jun 02, 2014 7:17 am
Are you a spam bot?: No

Re: Expenses vs Deposit comparison

Post by aramacciotti »

Nikolay wrote:super!!!
My improuvments:

Code: Select all

select strftime('%m', c.TRANSDATE) as MONTH
    ,strftime('%Y', c.TRANSDATE) as YEAR
    , total(case c.TRANSCODE when 'Deposit'  then TRANSAMOUNT * cf.BaseConvRate end) as TotalDeposits
    , total(case c.TRANSCODE when 'Withdrawal' then TRANSAMOUNT * cf.BaseConvRate end) as TotalExpenses
from CHECKINGACCOUNT_V1 c
    left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID = c.ACCOUNTID
    left join currencyformats_v1 cf on cf.currencyid=AC.currencyid
where TRANSDATE >= '2009-01-01'
and transcode != 'Transfer'
and c.status !='V'
and ac.status !='Closed'
group by year, month
order by year,month

Code: Select all

local deposit = '';
local depositdata = '';
local expense = '';
local expensedata = '';
local month = '';
local months = {['01']='Jan', ['02']='Feb', ['03']='Mar', ['04']='Apr', ['05']='May', ['06']='Jun', ['07']='Jul', ['08']='Aug', ['09']='Sep', ['10']='Oct', ['11']='Nov', ['12']='Dec'};
function handle_record(record)
    depositdata = depositdata .. record:get("TotalDeposits") .. ',';
    expensedata = expensedata .. record:get("TotalExpenses") .. ',';
    local entryDate = months[record:get("MONTH")] .. '-' .. record:get("YEAR");
    month = month .. '"' .. entryDate .. '",';
    record:set('ENTRY_DATE', entryDate)
end
function complete(result)
    deposit = [[
        {label:"Deposits",
        fillColor : "rgba(151,187,205,0.2)",
        strokeColor : "rgba(151,187,205,1)",
        pointColor : "rgba(151,187,205,1)",
        pointStrokeColor:"#fff",
        pointHighlightFill: "#fff",
        pointHighlightStroke: "rgba(151,187,205,1)",
        data : [%s]}
    ]]
    deposit = string.format(deposit, depositdata);
    expense = expense .. '{label:"Expenses",fillColor : "rgba(253,17,2,0.2)", strokeColor : "rgba(253,17,2,1)", pointColor : "rgba(253,17,2,1)",pointStrokeColor:"#fff",pointHighlightFill: "#fff",pointHighlightStroke: "rgba(253,17,2,1)", data : [';
    expense = expense .. string.sub(expensedata,1,-2) .. ']}';
    result:set('TREND_DEPOSITS', deposit);
    result:set('TREND_EXPENSES', expense);
    result:set('TREND_MONTHS', string.sub(month,1,-2));
end

Code: Select all

<!DOCTYPE html><h3><TMPL_VAR REPORTNAME></h3>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
 <title><TMPL_VAR REPORTNAME></title> 
<link href = "master.css" rel = "stylesheet" /> 
<script src = "Chart.js"></script> 
</head>
<body>
    <div class="container">
<TMPL_VAR TODAY><hr>

<canvas id="reportChart" width="1200" height="600"></canvas></td></tr>
<table class='table'>
    <thead>
    <tr>
        <th>Month/Year</th>
        <th>Total Deposit</th>
        <th>Total Expenses</th>
     </tr>
    </thead>
    <tbody>
    <TMPL_LOOP NAME=CONTENTS>
    <tr>
        <td><TMPL_VAR "ENTRY_DATE"></td>
        <td class='money'><TMPL_VAR "TotalDeposits"></td>
        <td class='money'><TMPL_VAR "TotalExpenses"></td>
        </tr>
    </TMPL_LOOP>
 </tbody>
</table>
<TMPL_LOOP ERRORS>
    <hr>
    <TMPL_VAR ERROR>
</TMPL_LOOP>
    </div>   
</body>
<script type="text/javascript">
    <!-- Chart -->
    var data = {
        labels : [<TMPL_VAR "TREND_MONTHS">],
        datasets : [<TMPL_VAR "TREND_DEPOSITS">,<TMPL_VAR "TREND_EXPENSES">]
    }
    var ctx = document.getElementById("reportChart").getContext("2d");
    var reportChart = new Chart(ctx).Line(data);
</script>

</html>
Hi Nikolay, would you like to share the expected results as Renato did? It could be useful to understand the code changes.
aramacciotti
Senior MMEX User
Posts: 25
Joined: Mon Jun 02, 2014 7:17 am
Are you a spam bot?: No

Re: Expenses vs Deposit comparison

Post by aramacciotti »

I upload an updated version that includes the  Renato suggestions and few changes I did in the meantime.
Attachments
Expenses vs Deposits comparison.grm
(1.97 KiB) Downloaded 597 times
User avatar
Renato
MVP MMEX User
Posts: 655
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Expenses vs Deposit comparison

Post by Renato »

Hello Nikolay

Sure there is a miscalculation in the accounts with foreign currency because the current price it is taken for the calculation.
True, the price of those days when booking would be was up to date.
The made ​​use of in MMEX method of treatment of foreign currency, leaves no evaluations to in the past.

I wished that I can enter in any foreign currency booking the course manually respectively override the current default.
This of course, that with each booking, the price will be saved.
Renato Forum Administrator
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Expenses vs Deposit comparison

Post by Nikolay »

One more report based on this idea with table sorter
(for more detailes http://forum.moneymanagerex.org/viewtop ... =16&t=5543)

PS to avoid miscalculation I suggest used one or two main accounts by including
and ac.ACCOUNTNAME in ('First account name', 'second one')
both should be with the same currency

2 aramacciotti
To understand code changes (you mean sql part?) create test Db with 2 account - one, for example with USD and anothe RUB.
put 1 account one 1 $ expenses
put to account two 1 RUB
base currency is USD.
Update currencies rates.
Total should be 1.02$
But in your report it will be 2$
2.png
2.png (14.2 KiB) Viewed 6691 times
Attachments
Expenses vs Deposits comparison last 12 months Bar.grm
(2.33 KiB) Downloaded 595 times
fabianfp
New MMEX User
Posts: 10
Joined: Sun Sep 28, 2014 10:29 pm
Are you a spam bot?: No

Re: Expenses vs Deposit comparison

Post by fabianfp »

I use this report but i have a problem. The result don´t considers the coute of differents coins. ¡¿Do you know how do this? Thanks !
Post Reply