Expenses vs Deposit comparison
Moderator: Renato
-
- Senior User
- Posts: 25
- Joined: Mon Jun 02, 2014 7:17 am
- Are you a spam bot?: No
Expenses vs Deposit comparison
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.
This report will graph the monthly values of Deposits and Expenses (Withdrawals) and report the same in a table under the graph itself.
You do not have the required permissions to view the files attached to this post.
-
- MVP User
- Posts: 684
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: Expenses vs Deposit comparison
Hello aramacciotti
Super, thank you very much.
Had even a suggestion for improvement:
instead of
and at the conclusion of the insert script
and here is the result, but for me useless, because the shares with MMEX can not be performed as accounting.
Super, thank you very much.
Had even a suggestion for improvement:
instead of
use
<td><TMPL_VAR "TotalDeposits"></td>
<td><TMPL_VAR "TotalExpenses"></td>
Code: Select all
<td class = "money, text-right"><TMPL_VAR "TotalDeposits"></td>
<td class = "money, text-right"><TMPL_VAR "TotalExpenses"></td>
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>
You do not have the required permissions to view the files attached to this post.
Renato Forum Administrator
-
- 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
super!!!
My improuvments:
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>
-
- 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
the package
You do not have the required permissions to view the files attached to this post.
-
- MVP User
- Posts: 684
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: Expenses vs Deposit comparison
Hello Nikolay
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.
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
-
- Senior User
- Posts: 25
- Joined: Mon Jun 02, 2014 7:17 am
- Are you a spam bot?: No
Re: Expenses vs Deposit comparison
Hi Nikolay, would you like to share the expected results as Renato did? It could be useful to understand the code changes.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>
-
- Senior User
- Posts: 25
- Joined: Mon Jun 02, 2014 7:17 am
- Are you a spam bot?: No
Re: Expenses vs Deposit comparison
I upload an updated version that includes the Renato suggestions and few changes I did in the meantime.
You do not have the required permissions to view the files attached to this post.
-
- MVP User
- Posts: 684
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: Expenses vs Deposit comparison
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.
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
-
- 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
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$
(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$
You do not have the required permissions to view the files attached to this post.
-
- New User
- Posts: 10
- Joined: Sun Sep 28, 2014 10:29 pm
- Are you a spam bot?: No
Re: Expenses vs Deposit comparison
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 !