Budget Monitoring

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

Moderator: Renato

nuvito
New MMEX User
Posts: 10
Joined: Mon Jun 11, 2012 5:15 pm
Are you a spam bot?: No

Budget Monitoring

Post by nuvito »

Monitor income and expenses of the current month in comparison to budget as gauge charts.

For gauge chart creation ChartJS from http://chartjs.devexpress.com/ is used, which have to be downloaded and unpacked into MoneyManagerEX/res directory. Report works with instable version 6254.

The report package (see below) contains a description of this report, as well as what are the prerequesites for this report to work.

SQL:

Code: Select all

/**
 * Budget: One month is calculated by 365 / 12 = 30.41667, so that one month contains 30.41667 days.
 * 		This value will be used to calculate weekly, bi-weekly or daily budget for a month.
 */
select dic.TransCode, dic.CategoryId, dic.Category Category
	, ifnull(round(ActualAmount, 2), 0) ActualAmount, ifnull(round(BudgetAmount, 2), 0) BudgetAmount
	, ifnull(round((ifnull(ActualAmount, 0) / BudgetAmount) * 100, 2), 1000) Rate
from (
    select distinct TransCode, CategId CategoryId, CategName category 
    from category_v1 
    	join checkingaccount_v1 using (CategId) 
	where 1=1
		and TransCode <> 'Transfer'
) dic 
    left join (
		select CategoryId, Category, total(BasedTransAmount) ActualAmount
        from (
            select ifnull(st.CategId, t.CategId) CategoryId, c.CategName category
            	, ifnull(st.SplitTransAmount, t.TransAmount) * cf.BaseConvRate BasedTransAmount
            from (checkingaccount_v1 t
		left join SplitTransactions_v1 st on (t.TransId = st.TransId))
                join Category_v1 c on (c.CategId = ifnull(st.CategId, t.CategId))
                join AccountList_v1 a on (t.AccountId = a.AccountId)
                join CurrencyFormats_v1 cf on (a.CurrencyId = cf.CurrencyId)
            where 1=1
		and t.TransCode <> 'Transfer'
                and t.Status <> 'V'
                and TransDate between date('now', 'start of month') and date('now', 'start of month', '+1 month', '-1 day')
        )
        group by CategoryId, Category
	) d on (dic.CategoryId = d.CategoryId)
	left join (
		select c.CategId CategoryId, c.CategName Category
			, total(abs(
				case 
					when Period = 'Weekly' then Amount * 30.41667 / 7
					when Period = 'Bi-Weekly' then Amount * 30.41667 / 7 / 2
					when Period = 'Monthly' then Amount 
					when Period = 'Bi-Monthly' then Amount / 2
					when Period = 'Quarterly' then Amount / 3
					when Period = 'Half-Yearly' then Amount / 6
					when Period = 'Yearly' then Amount / 12
					when Period = 'Daily' then Amount * 30.41667
				end
			)) BudgetAmount
		from BudgetTable_v1 bt
		    join BudgetYear_v1 by on (bt.BudgetYearId = by.BudgetYearId)
		    join Category_v1 c on (bt.CategId = c.CategId)
		where 1=1
		    and by.BudgetYearName = strftime('%Y-%m', date())
		group by c.CategId, c.CategName
	) b on (dic.CategoryId = b.CategoryId)
where 1=0
	or ifnull(ActualAmount, 0) != 0
	or ifnull(BudgetAmount, 0) != 0
order by dic.TransCode, dic.Category
;
Lua:

Code: Select all

local dashboardChartsJsOutput = '';
---
-- The color definition of withdrawal colors. The "start" marks the left including value of
-- the range, the "end" marks the right excluding value.
-- 
-- The first 100% of withdrawal colors are green, as it's better be under budget.
-- 
local withdrawalColors = {};
withdrawalColors[1] = { ["start"] = -1, ["end"] = 25, ["color"] = "#009900"};
withdrawalColors[2] = { ["start"] = 25, ["end"] = 50, ["color"] = "#00CC00"};
withdrawalColors[3] = { ["start"] = 50, ["end"] = 75, ["color"] = "#00EE00"};
withdrawalColors[4] = { ["start"] = 75, ["end"] = 100, ["color"] = "#44FF00"};
withdrawalColors[5] = { ["start"] = 100, ["end"] = 105, ["color"] = "#88FF00"};
withdrawalColors[6] = { ["start"] = 105, ["end"] = 110, ["color"] = "#BBFF00"};
withdrawalColors[7] = { ["start"] = 110, ["end"] = 115, ["color"] = "#FFDD00"};
withdrawalColors[8] = { ["start"] = 115, ["end"] = 120, ["color"] = "#FFBB00"};
withdrawalColors[9] = { ["start"] = 120, ["end"] = 130, ["color"] = "#FF8800"};
withdrawalColors[10] = { ["start"] = 130, ["end"] = 150, ["color"] = "#FF4400"};
withdrawalColors[11] = { ["start"] = 150, ["end"] = 170, ["color"] = "#EE0000"};
withdrawalColors[12] = { ["start"] = 170, ["end"] = 190, ["color"] = "#CC0000"};
withdrawalColors[13] = { ["start"] = 190, ["end"] = 9999, ["color"] = "#990000"};

---
-- The color definition of desposit colors. 
-- 
-- The first 90% of deposit colors are red, as it's better to be over budget.
-- 
local depositColors = {};
depositColors[1] = { ["start"] = -1, ["end"] = 10, ["color"] = "#990000"};
depositColors[2] = { ["start"] = 10, ["end"] = 30, ["color"] = "#CC0000"};
depositColors[3] = { ["start"] = 30, ["end"] = 50, ["color"] = "#EE0000"};
depositColors[4] = { ["start"] = 50, ["end"] = 70, ["color"] = "#FF4400"};
depositColors[5] = { ["start"] = 70, ["end"] = 80, ["color"] = "#FF8800"};
depositColors[6] = { ["start"] = 80, ["end"] = 85, ["color"] = "#FFBB00"};
depositColors[7] = { ["start"] = 85, ["end"] = 90, ["color"] = "#FFDD00"};
depositColors[8] = { ["start"] = 90, ["end"] = 95, ["color"] = "#BBFF00"};
depositColors[9] = { ["start"] = 95, ["end"] = 100, ["color"] = "#88FF00"};
depositColors[10] = { ["start"] = 100, ["end"] = 125, ["color"] = "#44FF00"};
depositColors[11] = { ["start"] = 125, ["end"] = 150, ["color"] = "#00EE00"};
depositColors[12] = { ["start"] = 150, ["end"] = 175, ["color"] = "#00CC00"};
depositColors[13] = { ["start"] = 175, ["end"] = 9999, ["color"] = "#009900"};

---
-- The sum of the expenses budget over all category budgets
-- 
local budgetExpenseSum = 0;

---
-- The sum of the income budget over all category budgets
-- 
local budgetIncomeSum = 0;

---
-- The sum of all actual expenses over all categories
-- 
local actualExpenseSum = 0;

---
-- The sum of all actual incomes over all categories
-- 
local actualIncomeSum = 0;

---
-- Create the javascript chart code for the given category record, which contains the Code of the
-- transaction (Withdrawal or Deposit), the c ategory id, the category name, the budget amount
-- and the actual amount. The rate determines the quotient of actual amount and budget amount.
-- 
-- @param #table record a record of the database query
-- 
function createChartCodeForCategoryBudget(record) 
    local template = [==[
        var c%d = $('<div style="width: 125px; float: left; margin: 0 auto;" />').appendTo('#chartContainer%s');
        c%d.dxLinearGauge($.extend(true, {}, linearGaugeOptions, {
            title: { text: '%s', },
            scale: {
                startValue: 0, 
                endValue: %d, 
                majorTick: { customTickValues: [%d], },
            },
            value: %f,
            valueIndicator: { color: '%s', },
        }));
    ]==];
    local colorDefinition = withdrawalColors;
    if record:get("TransCode") == "Deposit" then
        colorDefinition = depositColors;
    end
    
    local color = determineRateColor(record:get("Rate"), colorDefinition);
    local js = string.format(template
        , record:get("CategoryId")
        , record:get("TransCode")
        , record:get("CategoryId")
        , record:get("Category")
        , 2 * record:get("BudgetAmount")
        , record:get("BudgetAmount"), record:get("ActualAmount")
        , color);
    
    return js;
end

---
-- Create the javascript chart code for comparison of two values. The given title is used as chart
-- title, the type specifies if its a expense or income chart (regarding color definition).
-- 
-- @param #string title the title of the chart
-- @param #float compareValue the comparison value which is displayed as value of the gauge
-- @param #float referenceValue the reference value which is displayed as subvalue at "12 o'clock"
-- @param #string type type of color definition expenses or income
-- 
function createChartCodeForComparison(title, compareValue, referenceValue, type)
    local normTitle = string.gsub(title, "%A", "");
    local template = [==[
        var d%s = $('<div style="width: 380px; float: left; margin: 0 auto;" />').appendTo('#chartContainerOverall');
        d%s.dxCircularGauge($.extend(true, {}, circularGaugeOptions, {
                title: { text: '%s', },
                scale: {
                    startValue: 0, 
                    endValue: %d, 
                },
                rangeContainer: {
                    ranges: [
                        %s
                    ],
                },
                value: %f,
                subvalues: [ %f ],
            }));
    ]==];
    
    local colorDefinition = withdrawalColors;
    if type == "Income" then
        colorDefinition = depositColors;
    end
    
    local jsRangeContainerTemplate = determineRangeColorsAsJs(referenceValue, colorDefinition);
    
    local js = string.format(template
        , normTitle
        , normTitle
        , title
        , 2 * referenceValue
        , jsRangeContainerTemplate
        , compareValue
        , referenceValue
    );
    
    return js;
end

---
-- Determine the colored ranges of the chart by the color definition. The maxValue is used to
-- calculate real values by the percentage values of the color definition.
-- 
-- The ranges will be returned as javascript code, which will be inserted into the chart options.
-- 
-- @param #float maxValue the maximum value of the ranges
-- @param #table colorDefinition defines the ranges and their colors
-- 
function determineRangeColorsAsJs(maxValue, colorDefinition)
    local js = "";
    local rangeTemplate = [==[
        { startValue: %d, endValue: %d, color: '%s' },
    ]==];
    for i = 1, #colorDefinition do
        js = js .. string.format(rangeTemplate
            , maxValue * (colorDefinition[i]["start"] / 100)
            , maxValue * (colorDefinition[i]["end"] / 100)
            , colorDefinition[i]["color"]
        );
    end
    
    return js;
end

---
-- Determine the color for the given rateString from the specified colorDefinition. If the range
-- has been found for the given rate, the according color will be returned.
-- 
-- @param #string rateString the rate which should be colored
-- @param #table colorDefinition defines the ranges and their colors
-- 
function determineRateColor(rateString, colorDefinition)
    local rate = tonumber(rateString); 
    for i = 1, #colorDefinition do
        if colorDefinition[i]["start"] < rate and rate <= colorDefinition[i]["end"] then
            color = colorDefinition[i]["color"];
            break;
        end
    end
        
    return color;
end

---
-- Update the sum values by the actual and budget amount of the record, dependent on the transaction
-- code
-- 
-- @param #table record the record of the database query
-- 
function updateAmountSumsBy(record) 
    if record:get("TransCode") == "Withdrawal" then
        budgetExpenseSum = budgetExpenseSum + record:get("BudgetAmount");
        actualExpenseSum = actualExpenseSum + record:get("ActualAmount");
    elseif record:get("TransCode") == "Deposit" then
        budgetIncomeSum = budgetIncomeSum + record:get("BudgetAmount");
        actualIncomeSum = actualIncomeSum + record:get("ActualAmount");
    end
end

---
-- Handle each record which will be returned by the database query
-- 
-- @param #table record the record of the database query
-- 
function handle_record(record) 
    local jsOutputPerRecord = createChartCodeForCategoryBudget(record);
    
    updateAmountSumsBy(record);
    
    dashboardChartsJsOutput = dashboardChartsJsOutput .. jsOutputPerRecord;
end

---
-- Function will be called after all records have been handled. The chart code for the sum values
-- will be created and put into a result table, which can be inserted within the template
-- 
-- @param #table result placeholder for javascript code to be inserted into the template
-- 
function complete(result)
    if actualIncomeSum == 0 then
        actualIncomeSum = actualExpenseSum / 2;
    end
    local jsExpenseIncome = createChartCodeForComparison("Expenses vs Income"
        , actualExpenseSum, actualIncomeSum, "Expense");
    local jsIncomeBudget = createChartCodeForComparison("Income vs Budget"
        , actualIncomeSum, budgetIncomeSum, "Income");
    local jsExpenseBudget = createChartCodeForComparison("Expense vs Budget"
        , actualExpenseSum, budgetExpenseSum, "Expense");
    
    result:set("EXPENSE_INCOME_DATA", jsExpenseIncome);
    result:set("INCOME_BUDGET_DATA", jsIncomeBudget);
    result:set("EXPENSE_BUDGET_DATA", jsExpenseBudget);
    result:set("DASHBOARD_DATA", dashboardChartsJsOutput);
end
Template:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title><TMPL_VAR name="REPORTNAME" /></title>
    <link href="master.css" rel="stylesheet"></link>
    <script src="jquery-2.0.3.min.js" type="text/javascript"></script>
    <script src="globalize.min.js" type="text/javascript"></script>
    <script src="dx.chartjs.js" type="text/javascript"></script>
    <script type="text/javascript">
        var circularGaugeOptions = {
            scale: {
                majorTick: {
                    tickInterval: 500,
                    color: '#000000',
                }, 
                minorTick: {
                    visible: true,
                    tickInterval: 100,
                    color: '#000000',
                }, 
                label: {
                    customizeText: function(label) {
                        return label.valueText + ' &euro;';
                    },
                    indentFromTick: 15,
                },
            },
            rangeContainer: {
                palette: 'pastel',
                offset: -10,
            },
            title: {
                font: { size: 18 },
                position: 'top-center',
            },
            valueIndicator: {
                offset: 10,
                color: '#CC0000',
                text: {
                    format: 'fixedPoint',
                    precision: 2,
                    indent: 10,
                },
            },
            subvalueIndicator :{
                type: 'triangle',
                offset: -5,
                color: '#0033CC',
            },
        };
        var linearGaugeOptions = {
            geometry: {
                orientation: 'vertical',
            },
            scale: {
                majorTick: {
                    color: 'green',
                    showCalculatedTicks: false,
                },
                label: {
                    indentFromTick: -5,
                    font: {
                        color: 'green',
                    },
                    customizeText: function(label) {
                        return label.valueText + ' &euro;';
                    },
                } ,
            },
            rangeContainer: {
                offset: 5,
            }, 
            valueIndicator: {
                type: 'rangebar',
                offset: 5,
                text: {
                    indent: 3,
                    format: 'fixedPoint',
                    precision: 2,
                    customizeText: function(label) {
                        return label.valueText + ' &euro;';
                    },
                },
            },
            title: {
                font: { size: 12 },
                position: 'top-center',
            },
        };
        $(function () {
            <TMPL_VAR name="EXPENSE_INCOME_DATA" />
            <TMPL_VAR name="INCOME_BUDGET_DATA" />
            <TMPL_VAR name="EXPENSE_BUDGET_DATA" />
            <TMPL_VAR name="DASHBOARD_DATA" />
        });
    </script>
</head>
<body>
    <div class="page-header">
        <h2 class="text-center">Budget Monitoring for this month</h2>
        <p class="text-center">Compare Expenses, Income and Budget at <TMPL_VAR name="today" /></p>
    </div>
    <div id="chartContainerOverall" class="container" style="clear:both;"></div>
    <div id="chartContainerWithdrawal" class="container" style="height:350px;clear:both;">
        <h3>Budget monitoring of Withdrawal Categories</h3>
    </div>
    <div id="chartContainerDeposit" class="container" style="height:350px;clear:both;">
        <h3>Budget monitoring of Deposit Categories</h3>
    </div>
</body>
</html>
budget_monitoring_current_month.JPG
Screenshot
(68.46 KiB) Downloaded 2735 times
Kind regards,

nuvito
Attachments
BudgetMonitoringCurrentMonth.grm
Report package
(5.77 KiB) Downloaded 856 times
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Budget Monitoring

Post by Nikolay »

very nice .
danyall
New MMEX User
Posts: 5
Joined: Mon Mar 31, 2014 3:54 pm
Are you a spam bot?: No

Re: Budget Monitoring

Post by danyall »

Excelent! I was looking for that!

Is it possible to see the details also, i mean the top 10 categories with most difference?
danyall
New MMEX User
Posts: 5
Joined: Mon Mar 31, 2014 3:54 pm
Are you a spam bot?: No

Re: Budget Monitoring

Post by danyall »

Sorry but the graphics does not work even after installing the library.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Budget Monitoring

Post by Nikolay »

should work.
there are 5 files should be in res folder:
Chart.js globalize.min.js jquery-2.0.3.min.js
dx.chartjs.js master.css
nuvito
New MMEX User
Posts: 10
Joined: Mon Jun 11, 2012 5:15 pm
Are you a spam bot?: No

Re: Budget Monitoring

Post by nuvito »

danyall wrote:Is it possible to see the details also, i mean the top 10 categories with most difference?
The categories will be displayed, as long as there is either budget or actual value for this category. In my test database only one category ("bills") has budget and current value, so only this one is displayed.

Currently the categories are ordered by category name in the sql code. But you surely can change the order using the relative deviation:

first add the calculation of the relative deviation to select clause:

Code: Select all

       , (abs(ifnull(ActualAmount, 0) - ifnull(BudgetAmount, 0)) / ifnull(BudgetAmount, 0)) relativeDeviation
from ...
then add the relativeDeviation to the order by clause

Code: Select all

order by dic.TransCode,  relativeDeviation desc, dic.Category
Kind regards,

nuvito
nuvito
New MMEX User
Posts: 10
Joined: Mon Jun 11, 2012 5:15 pm
Are you a spam bot?: No

Re: Budget Monitoring

Post by nuvito »

danyall wrote:Sorry but the graphics does not work even after installing the library.
Did you enable browser emulation for mmex? See http://www.codelathe.com/forum/viewtopi ... =10#p10132.

Are the graphics displayed, if you open the created chart in your favorite browser at http://localhost:8080/ (while Mmex is also running)?
Kind regards,

nuvito
guanlisheng
MMEX Developer
Posts: 375
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Budget Monitoring

Post by guanlisheng »

Hi Nuvito, thank you for providing this wonderful report based on existing general report approach and your work is appreciated.
i am wondering would you have a step more to push it to general report collection github (https://github.com/moneymanagerex/general-reports) ?
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
nuvito
New MMEX User
Posts: 10
Joined: Mon Jun 11, 2012 5:15 pm
Are you a spam bot?: No

Re: Budget Monitoring

Post by nuvito »

Hi,
guanlisheng wrote:i am wondering would you have a step more to push it to general report collection github (https://github.com/moneymanagerex/general-reports) ?
I pushed my work to github and created a pull request. Please check, if it's suitable for you.
Kind regards,

nuvito
User avatar
avma
Senior MMEX User
Posts: 30
Joined: Wed Sep 02, 2009 6:47 am
Are you a spam bot?: No
Location: Israel

Re: Budget Monitoring

Post by avma »

nuvito wrote:
danyall wrote:Sorry but the graphics does not work even after installing the library.
Did you enable browser emulation for mmex? See http://www.codelathe.com/forum/viewtopi ... =10#p10132.

Are the graphics displayed, if you open the created chart in your favorite browser at http://localhost:8080/ (while Mmex is also running)?
It does not work for me either.
i did all necessary steps as described above but still getting a blank reports with headers only, same thing when i open the link http://localhost:8080/ in google chrome.
i'm running on win 8.1 64 (up to date)
Post Reply