Budget Monitoring

26 posts
Budget Monitoring

nuvito
New MMEX User

Posts: 17
Joined: Mon Jun 11, 2012 11:15 am
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 368 times
Re: Budget Monitoring

Nikolay
MMEX Developer

Posts: 2255
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
very nice .
Re: Budget Monitoring

danyall
New MMEX User

Posts: 6
Joined: Mon Mar 31, 2014 9:54 am
Excelent! I was looking for that!

Is it possible to see the details also, i mean the top 10 categories with most difference?
Re: Budget Monitoring

danyall
New MMEX User

Posts: 6
Joined: Mon Mar 31, 2014 9:54 am
Sorry but the graphics does not work even after installing the library.
Re: Budget Monitoring

Nikolay
MMEX Developer

Posts: 2255
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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
Re: Budget Monitoring

nuvito
New MMEX User

Posts: 17
Joined: Mon Jun 11, 2012 11:15 am
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
Re: Budget Monitoring

nuvito
New MMEX User

Posts: 17
Joined: Mon Jun 11, 2012 11:15 am
danyall wrote:Sorry but the graphics does not work even after installing the library.


Did you enable browser emulation for mmex? See viewtopic.php?f=16&t=5446&start=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
Re: Budget Monitoring

guanlisheng
MMEX Developer

Posts: 274
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
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) ?
Re: Budget Monitoring

nuvito
New MMEX User

Posts: 17
Joined: Mon Jun 11, 2012 11:15 am
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
Re: Budget Monitoring

avma
Super MMEX User

Posts: 43
Joined: Wed Sep 02, 2009 12:47 am
Location: Israel
nuvito wrote:
danyall wrote:Sorry but the graphics does not work even after installing the library.


Did you enable browser emulation for mmex? See viewtopic.php?f=16&t=5446&start=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)
Who is online

Users browsing this forum: Google [Bot] and 2 guests