Expenses vs Deposit comparison

Page 1 of 1
10 posts
Expenses vs Deposit comparison

aramacciotti
Super MMEX User

Posts: 40
Joined: Mon Jun 02, 2014 1:17 am
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 307 times
Re: Expenses vs Deposit comparison
User avatar
Renato
Super MMEX User

Posts: 106
Joined: Mon Apr 25, 2011 1:36 pm
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
regards Renato

use MMEX since 2009
Re: Expenses vs Deposit comparison

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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>
Re: Expenses vs Deposit comparison

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
the package 
Attachments
Expenses vs Deposits comparison.grm
(2.25 KiB) Downloaded 263 times
Re: Expenses vs Deposit comparison
User avatar
Renato
Super MMEX User

Posts: 106
Joined: Mon Apr 25, 2011 1:36 pm
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.
regards Renato

use MMEX since 2009
Re: Expenses vs Deposit comparison

aramacciotti
Super MMEX User

Posts: 40
Joined: Mon Jun 02, 2014 1:17 am
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.
Re: Expenses vs Deposit comparison

aramacciotti
Super MMEX User

Posts: 40
Joined: Mon Jun 02, 2014 1:17 am
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 251 times
Re: Expenses vs Deposit comparison
User avatar
Renato
Super MMEX User

Posts: 106
Joined: Mon Apr 25, 2011 1:36 pm
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.
regards Renato

use MMEX since 2009
Re: Expenses vs Deposit comparison

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
One more report based on this idea with table sorter
(for more detailes viewtopic.php?f=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
(14.2 KiB) Downloaded 1673 times
Attachments
Expenses vs Deposits comparison last 12 months Bar.grm
(2.33 KiB) Downloaded 270 times
Re: Expenses vs Deposit comparison

fabianfp
New MMEX User

Posts: 16
Joined: Sun Sep 28, 2014 4:29 pm
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 !
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 2 guests