Forecast Reports

Page 1 of 1
10 posts
Forecast Reports

siena123
MMEX Developer

Posts: 12
Joined: Wed Jan 23, 2013 7:00 pm
Disclaimer: Forecast accuracy depends on correlating the method used to the data. These are only intended to be an example of some different methods and how they can be implemented.

  • Asset Forecast Report (uses the appreciation/depreciation rates entered for each asset)

  • Asset Forecast Report.grm
    (2.21 KiB) Downloaded 251 times

    SQL
    Code: Select all
    SELECT STARTDATE, VALUE, VALUECHANGE, VALUECHANGERATE
    FROM ASSETS_V1;

    Lua
    Code: Select all
    local total = 0;
    local forecast = {0, 0, 0, 0, 0}
    local period = 5;

    function is_leap_year(year)
        local ly = 0;
        if year % 4 == 0 then
            if year % 100 == 0 then
                if year % 400 == 0 then
                    ly = 1;
                end
            else
                ly = 1;
            end
        end
        return ly;
    end

    function get_days(month, year)
        local days = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }
        local d = days[tonumber(month)];
        if tonumber(month) == 2 and is_leap_year(year) ~= 0 then
            d = 29;
        end
        return d;
    end

    function get_years(record)
        local date = record:get("STARTDATE");
        local aYear = string.sub(date,1,4);
        local aMonth = string.sub(date,6, 7);
        local aDay = string.sub(date,9);
        local cYear = os.date('%Y');
        local cMonth = os.date('%m');
        local cDay = os.date('%d');
        local nbrYears = 0;
        if aYear == cYear then
            local days = 0;
            if aMonth == cMonth then
                days = cDay - aDay;
            else
                days = get_days(aMonth, aYear) - aDay;
                if tonumber(aMonth) + 1 > tonumber(cMonth) - 1 then
                    for i = (aMonth + 1), (cMonth - 1) do
                        days = days + get_days(i, aYear);
                    end
                end
                days = days + cDay;
            end
            if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then
                nbrYears = days / 366;
            else
                nbrYears = days / 365;
            end
        else
            nbrYears = cYear - aYear;
            local days = get_days(aMonth, aYear) - aDay;
            for i = (aMonth + 1), 12 do
                days = days + get_days(i, aYear);
            end
            if tonumber(cMonth) > 1 then
                for i = 1, (cMonth - 1) do
                    days = days + get_days(i, cYear);
                end
            end
            days = days + cDay;
            if tonumber(aMonth) < tonumber(cMonth) then
                nbrYears = nbrYears - 1;
            elseif aMonth == cMonth and tonumber(aDay) <= tonumber(cDay) then
                    nbrYears = nbrYears - 1;
            end
            if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then
                nbrYears = nbrYears + (days / 366);
            else
                nbrYears = nbrYears + (days / 365);
            end
        end
        return nbrYears;
    end

    function handle_record(record)
        local value = record:get("VALUE");
        local fvalue = {}
        for i = 1,period do
            fvalue[i] = value;
        end
        local change = record:get("VALUECHANGE");
        if change ~= 'None' then
            local rate = record:get("VALUECHANGERATE") / 100;
            local years = get_years(record);
            local scale = 1;
            local fscale = {}
            for i = 1,period do
                fscale[i] = scale;
            end
            if change == 'Appreciates' then
                scale = math.pow(1 + rate, years);
                for i = 1,period do
                    fscale[i] = math.pow(1 + rate, years + i);
                end
            elseif change == 'Depreciates' then
                    scale = math.pow(1 - rate, years);
                    for i = 1,period do
                        fscale[i] = math.pow(1 - rate, years + i);
                    end
            end
            value = value * scale;
            for i = 1,period do
                fvalue[i] = fvalue[i] * fscale[i];
            end
        end
        total = total + value;
        for i = 1,period do
            forecast[i] = forecast[i] + fvalue[i];
        end
    end

    function complete(result)
        local curYear = os.date('%Y');
        local curMonth = os.date('%m');
        local curDay = os.date('%d');
        result:set("Current_Total", total);
        for i = 1,period do
            result:set("Year" .. i .. "_Total", forecast[i]);
            local date = os.date("*t", os.time{year=curYear+i,month=curMonth,day=curDay});
            result:set("Year" .. i .. "_Label", string.format("(%d-%02d-%02d)",date.year,date.month,date.day));
        end
    end

    Template
    Code: Select all
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
    <html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <title><TMPL_VAR REPORTNAME></title>
        <link href = "master.css" rel = "stylesheet">
    </head>
    <body>
        <div class="container">
            <h3>Asset Forecast Report</h3>
            <p><TMPL_VAR TODAY></p>
            <div class="row">
                <div class="col-xs-2"></div>
                <div class="col-xs-8">
                    <table class="table">
                    <thead>
                      <tr>
                        <th>Date</th>
                        <th class="text-right">Value</th>
                      </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <td>Current</td>
                            <td class="money text-right"><TMPL_VAR Current_Total></td>
                        </tr>
                    </tbody>
                    <tfoot>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>1 year <TMPL_VAR Year1_Label></td>
                            <td class="money text-right"><TMPL_VAR Year1_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>2 years <TMPL_VAR Year2_Label></td>
                            <td class="money text-right"><TMPL_VAR Year2_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>3 years <TMPL_VAR Year3_Label></td>
                            <td class="money text-right"><TMPL_VAR Year3_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>4 years <TMPL_VAR Year4_Label></td>
                            <td class="money text-right"><TMPL_VAR Year4_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>5 years <TMPL_VAR Year5_Label></td>
                            <td class="money text-right"><TMPL_VAR Year5_Total></td>
                       </tr>
                    </tfoot>
                    </table>
                </div>
                <table class="table">
                    <tbody>
                    <TMPL_LOOP ERRORS>
                        <tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
                        <tr><td><TMPL_VAR ERROR></td></tr>
                    </TMPL_LOOP>
                    </tbody>
                </table>
            </div>
        </div>   
    </body>
    <script type="text/javascript">
        <!-- Format numbers -->
        function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
        var elements= document.getElementsByClassName("money");
        for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
    </script>
    </html>

  • Account Forecast Report (uses data from repeating transactions)

  • Account Forecast Report.grm
    (2.99 KiB) Downloaded 343 times

    SQL (Update account name in line 24.)
    Code: Select all
    select TRANSAMOUNT, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE,
    (
        select INITIALBAL + total(TRANSAMOUNT)
        from
            (select ACCOUNTID, STATUS,
                (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
            from CHECKINGACCOUNT_V1
            union all
            select TOACCOUNTID, STATUS, TOTRANSAMOUNT
            from CHECKINGACCOUNT_V1
            where TRANSCODE = 'Transfer') as t
        where  t.ACCOUNTID = a.ACCOUNTID
            and t.STATUS <> 'V') as Balance
    from
        (select ACCOUNTID, STATUS, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE,
            (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
        from BILLSDEPOSITS_V1
        union all
        select TOACCOUNTID, STATUS, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE, TOTRANSAMOUNT
        from BILLSDEPOSITS_V1
        where TRANSCODE = 'Transfer') as b
    inner join  ACCOUNTLIST_V1 as a
    on b.ACCOUNTID = a.ACCOUNTID
    where ACCOUNTNAME = 'Account1'
    and b.STATUS <> 'V';

    Lua
    Code: Select all
    local total = 0;
    local forecast = {}
    local period = 6;
    local initialized = 0;
    local repeatcode = 0;
    local numrepeats = 0;

    function is_leap_year(year)
        local ly = 0;
        if year % 4 == 0 then
            if year % 100 == 0 then
                if year % 400 == 0 then
                    ly = 1;
                end
            else
                ly = 1;
            end
        end
        return ly;
    end

    function get_days(month, year)
        local days = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }
        local d = days[tonumber(month)];
        if tonumber(month) == 2 and is_leap_year(year) ~= 0 then
            d = 29;
        end
        return d;
    end

    function get_time_difference(year, month, day)
        local curYear = os.date('%Y');
        local curMonth = os.date('%m');
        local curDay = os.date('%d');
        local d = day - curDay;
        local m = 0;
        if d < 0 then
            d = d + get_days(curMonth, curYear);
            m = -1;
        end
        m = m + month - curMonth;
        local y = 0;
        if m < 0 then
            m = m + 12;
            y = -1;
        end
        y = y + year - curYear;
        return {y, m, d}
    end

    function get_next_date(record, previousYear, previousMonth, previousDay)
        local originalnumber = tonumber(record:get("NUMOCCURRENCES"));
        -- Auto Execute User Acknowlegement required
        if repeatcode >= 100 then
            repeatcode = repeatcode - 100;
        end
        -- Auto Execute Silent mode
        if repeatcode >= 100 then
            repeatcode = repeatcode - 100;
        end
        if repeatcode ~= -1 then
            if repeatcode < 11 or repeatcode > 14 then
                numrepeats = numrepeats - 1;
            end
        end
        local nextDate= os.time{year=previousYear,month=previousMonth,day=previousDay}
        local updatedate = 0;
        if originalnumber == -1 then
            updatedate = 1;
        elseif numrepeats > 0 then
            updatedate = 1;
        end
        if updatedate ~= 0 then
            -- weekly
            if repeatcode == 1 then
                nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 7}
            -- biweekly
            elseif repeatcode == 2 then
                nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 14}
            -- month
            elseif repeatcode == 3 then
                nextDate = os.time{year=previousYear,month=previousMonth + 1,day=previousDay}
            -- bimonth
            elseif repeatcode == 4 then
                nextDate = os.time{year=previousYear,month=previousMonth + 2,day=previousDay}
            -- quarterly
            elseif repeatcode == 5 then
                nextDate = os.time{year=previousYear,month=previousMonth + 3,day=previousDay}
            -- half yearly
            elseif repeatcode == 6 then
                nextDate = os.time{year=previousYear,month=previousMonth + 6,day=previousDay}
            -- yearly
            elseif repeatcode == 7 then
                nextDate = os.time{year=previousYear + 1,month=previousMonth,day=previousDay}
            -- quad monthly
            elseif repeatcode == 8 then
                nextDate = os.time{year=previousYear,month=previousMonth + 4,day=previousDay}
            -- quad weekly
            elseif repeatcode == 9 then
                nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 28}
            -- daily
            elseif repeatcode == 10 then
                nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 1}
            -- repeat in X days or repeat in X months
            elseif repeatcode == 11 or repeatcode == 12 then
                if numrepeats ~= -1 then
                    numrepeats = -1;
                end
            -- every X days
            elseif repeatcode == 13 then
                nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + numrepeats}
            -- every X months
            elseif repeatcode == 14 then
                nextDate = os.time{year=previousYear,month=previousMonth + numrepeats,day=previousDay}
            -- month last day or monthly last business day
            elseif repeatcode == 15 or repeatcode == 16 then
                local m = previousMonth + 1;
                local y = previousYear;
                if m > 12 then
                    m = 1;
                    y = y + 1;
                end
                nextDate = os.time{year=y,month=m,day=get_days(m, y)}
                -- monthly last business day
                if repeatcode == 16 then
                    local n = os.date("*t", nextDate);
                    -- Sunday
                    if n.wday == 0 then
                        nextDate = os.time{year=n.year,month=n.month,day=n.day - 2}
                    -- Saturday
                    elseif n.wday == 7 then
                        nextDate = os.time{year=n.year,month=n.month,day=n.day - 1}
                    end
                end
            end
        end
        local next = os.date("*t", nextDate);
        return {next.year, next.month, next.day}
    end

    function handle_record(record)
        if initialized == 0 then
            total = record:get("Balance");
            for i=1,period do
                forecast[i] = total;
            end
            initialized = 1;
        end
        repeatcode = tonumber(record:get("REPEATS"));
        numrepeats = record:get("NUMOCCURRENCES");
        local amount = record:get("TRANSAMOUNT");
        local nextDate = record:get("NEXTOCCURRENCEDATE");
        local nextYear = tonumber(string.sub(nextDate,1,4));
        local nextMonth = tonumber(string.sub(nextDate,6, 7));
        local nextDay = tonumber(string.sub(nextDate,9));
        local timediff = get_time_difference(nextYear, nextMonth, nextDay);
        while (timediff[2] < period and timediff[1] == 0) or  timediff[1] < 0 do
            local m = timediff[2] + 1;
            if timediff[1] < 0 then
                m = 1;
            end
            for i=m,period do
                forecast[i] = forecast[i] + amount;
            end
            local next = get_next_date(record, nextYear, nextMonth, nextDay);
            if next[1] == nextYear and next[2] == nextMonth and next[3] == nextDay then
                break;
            end
            nextYear = next[1];
            nextMonth = next[2];
            nextDay = next[3];
            timediff = get_time_difference(nextYear, nextMonth, nextDay);
        end
    end

    function complete(result)
        local curYear = os.date('%Y');
        local curMonth = os.date('%m');
        local curDay = os.date('%d');
        result:set("Current_Total", total);
        for i=1,period do
            result:set("Month" .. i .. "_Total", forecast[i]);
            local date = os.date("*t", os.time{year=curYear,month=curMonth+i,day=curDay});
            result:set("Month" .. i .. "_Label", string.format("(%d-%02d-%02d)",date.year,date.month,date.day));
        end
    end

    Template
    Code: Select all
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
    <html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <title><TMPL_VAR REPORTNAME></title>
        <link href = "master.css" rel = "stylesheet">
    </head>
    <body>
        <div class="container">
            <h3>Account Forecast Report</h3>
            <p><TMPL_VAR TODAY></p>
            <div class="row">
                <div class="col-xs-2"></div>
                <div class="col-xs-8">
                    <table class="table">
                    <thead>
                      <tr>
                        <th>Date</th>
                        <th class="text-right">Value</th>
                      </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <td>Current</td>
                            <td class="money text-right"><TMPL_VAR Current_Total></td>
                        </tr>
                    </tbody>
                    <tfoot>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>1 month <TMPL_VAR Month1_Label></td>
                            <td class="money text-right"><TMPL_VAR Month1_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>2 months <TMPL_VAR Month2_Label></td>
                            <td class="money text-right"><TMPL_VAR Month2_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>3 months <TMPL_VAR Month3_Label></td>
                            <td class="money text-right"><TMPL_VAR Month3_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>4 months <TMPL_VAR Month4_Label></td>
                            <td class="money text-right"><TMPL_VAR Month4_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>5 months <TMPL_VAR Month5_Label></td>
                            <td class="money text-right"><TMPL_VAR Month5_Total></td>
                       </tr>
                       <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td>6 months <TMPL_VAR Month6_Label></td>
                            <td class="money text-right"><TMPL_VAR Month6_Total></td>
                       </tr>
                    </tfoot>
                    </table>
                </div>
                <table class="table">
                    <tbody>
                    <TMPL_LOOP ERRORS>
                        <tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
                        <tr><td><TMPL_VAR ERROR></td></tr>
                    </TMPL_LOOP>
                    </tbody>
                </table>
            </div>
        </div>   
    </body>
    <script type="text/javascript">
        <!-- Format numbers -->
        function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
        var elements= document.getElementsByClassName("money");
        for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
    </script>
    </html>

  • Account Trend with Forecast Report (uses linear regression)

  • Account Trend with Forecast Report.grm
    (2.66 KiB) Downloaded 229 times

    SQL (Update account name in line 29.)
    Code: Select all
    select strftime('%Y', TRANSDATE) as YEAR
        , strftime('%m', TRANSDATE) as MONTH
        , '' as DATE
        , total(TRANSAMOUNT)
            + (select INITIALBAL + total(TRANSAMOUNT)
                from
                    (select ACCOUNTID, TRANSDATE, STATUS,
                        (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as transamount
                    from CHECKINGACCOUNT_V1
                    union all
                    select TOACCOUNTID, TRANSDATE, STATUS, TOTRANSAMOUNT
                    from CHECKINGACCOUNT_V1
                    where TRANSCODE = 'Transfer') t2
                where t2.ACCOUNTID = t1.ACCOUNTID
                    and t2.STATUS <> 'V'
                    and (strftime('%Y', t2.TRANSDATE) < strftime('%Y', t1.TRANSDATE)
                        or (strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE)
                            and strftime('%m', t2.TRANSDATE) < strftime('%m', t1.TRANSDATE)))
            ) as Balance
    from
        (select ACCOUNTID, TRANSDATE, STATUS,
            (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as transamount
        from CHECKINGACCOUNT_V1
        union all
        select TOACCOUNTID, TRANSDATE, STATUS, TOTRANSAMOUNT
        from CHECKINGACCOUNT_V1
        where TRANSCODE = 'Transfer') t1
    inner join ACCOUNTLIST_V1 a on a.ACCOUNTID = t1.ACCOUNTID
    where ACCOUNTNAME = 'Account1'
        and t1.STATUS <> 'V'
    group by YEAR, MONTH
    order by YEAR asc, MONTH asc;

    Lua
    Code: Select all
    local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
    local labels = 'labels : [';
    local data = 'datasets : [{fillColor:"rgba(255,255,255,0)",strokeColor:"rgba(0,0,0,0.5)",pointColor:"rgba(0,0,0,0.5)",pointStrokeColor:"rgba(255,255,255,0)",data:[';
    -- forecast date
    local year = 0;
    local month = 0;
    -- Forecast line variables (Linear Regression)
    local count = 0;
    local count_sum = 0;
    local balance_sum = 0;
    local product_sum = 0;
    local squared_sum = 0;

    function handle_record(record)
        year = record:get("YEAR");
        month = record:get("MONTH");
        local date = months[tonumber(month)] .. ' ' .. year;
        labels = labels .. '"' .. date .. '",';
        record:set("DATE", date);
        local balance = string.format("%.2f", tonumber(record:get("Balance")));
        if tonumber(string.sub(balance,-1)) == 0  and tonumber(string.sub(balance,-2)) ~= 0 then
            data = data .. '\'' .. balance .. '\',';
        else
            data = data .. balance .. ',';
        end
    -- Update forecast line calculation values
        local x = count + 1;
        count = count + 1;
        count_sum = count_sum + x;
        balance_sum = balance_sum + balance;
        product_sum = product_sum + (x * balance);
        squared_sum = squared_sum + (x * x);
    end

    function complete(result)
    -- Forecast line equation
        local slope = ((count * product_sum) - (count_sum * balance_sum)) / ((count * squared_sum) - (count_sum * count_sum));
        local intercept = (balance_sum - (slope * count_sum)) / count;
    -- Forecast line values
        data = string.sub(data,1,-2) .. ']},{fillColor:"rgba(255,255,255,0)",strokeColor:"rgba(255,0,0,0.5)",pointColor:"rgba(255,0,0,0.5)",pointStrokeColor:"rgba(255,255,255,0)",data:[';
        for i=1,count do
            data = data .. ',';
        end
        for i=1,12 do
            -- Label
            month = month + 1;
            if month > 12 then
                year = year + 1;
                month = 1;
            end
            local date = months[tonumber(month)] .. ' ' .. year;
            labels = labels .. '"' .. date .. '",';
            result:set('FLABEL' .. i, date);
            -- Value
            local value = (slope * (count + i)) + intercept;
            local fval = string.format("%.2f", value);
            if tonumber(string.sub(fval,-1)) == 0  and tonumber(string.sub(fval,-2)) ~= 0 then
                data = data .. '\'' .. fval .. '\',';
            else
                data = data .. fval .. ',';
            end
            result:set('FVALUE' .. i, string.format("%.2f", value));
        end
        result:set('TREND_DATA', string.sub(labels,1,-2) .. "]," .. string.sub(data,1,-2) .. "]}]");
    end

    Template
    Code: Select all
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
    <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">
            <h3>Account Trend Report</h3>
            <p><TMPL_VAR TODAY></p>
            <div class="row">
                <div class="col-xs-2"></div>
                <div class="col-xs-8">
                    <table class "table-condensed">
                    <tr><td><canvas id="reportChart" width="600" height="300"></canvas></td></tr>
                    <tr><td>&nbsp;</td></tr>
                    <tr><td><table class="table">
                    <thead>
                      <tr>
                        <th>Date</th>
                        <th class="text-right">Balance</th>
                      </tr>
                    </thead>
                    <tbody>
                        <TMPL_LOOP NAME=CONTENTS>
                        <tr>
                            <td><TMPL_VAR DATE></td>
                            <td class="money text-right"><TMPL_VAR Balance></td>
                        </tr>
                        </TMPL_LOOP>
                    </tbody>
                    <tfoot>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL1></td>
                            <td class="money text-right"><TMPL_VAR FVALUE1></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL2></td>
                            <td class="money text-right"><TMPL_VAR FVALUE2></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL3></td>
                            <td class="money text-right"><TMPL_VAR FVALUE3></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL4></td>
                            <td class="money text-right"><TMPL_VAR FVALUE4></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL5></td>
                            <td class="money text-right"><TMPL_VAR FVALUE5></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL6></td>
                            <td class="money text-right"><TMPL_VAR FVALUE6></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL7></td>
                            <td class="money text-right"><TMPL_VAR FVALUE7></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL8></td>
                            <td class="money text-right"><TMPL_VAR FVALUE8></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL9></td>
                            <td class="money text-right"><TMPL_VAR FVALUE9></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL10></td>
                            <td class="money text-right"><TMPL_VAR FVALUE10></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL11></td>
                            <td class="money text-right"><TMPL_VAR FVALUE11></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL12></td>
                            <td class="money text-right"><TMPL_VAR FVALUE12></td>
                        </tr>
                    </tfoot>
                     </table></td></tr></table>
                </div>
                <table class="table">
                    <tbody>
                    <TMPL_LOOP ERRORS>
                        <tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
                        <tr><td><TMPL_VAR ERROR></td></tr>
                    </TMPL_LOOP>
                    </tbody>
                </table>
            </div>
        </div>   
    </body>
    <script type="text/javascript">
        <!-- Format numbers -->
        function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
        var elements= document.getElementsByClassName("money");
        for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
        <!-- Chart -->
        var data = {<TMPL_VAR "TREND_DATA">}
        var ctx = document.getElementById("reportChart").getContext("2d");
        var reportChart = new Chart(ctx).Line(data);
    </script>
    </html>

  • Category Trend with Forecast Report (uses historical average)

  • Category Trend with Forecast Report.grm
    (2.61 KiB) Downloaded 264 times

    SQL (Update category and subcategory names in line 21.)
    Code: Select all
    select strftime('%Y', t1.TRANSDATE) as YEAR
        , strftime('%m', t1.TRANSDATE) as MONTH
        , '' as DATE
        , total(t1.TRANSAMOUNT * c1.BASECONVRATE)
        + (select total(split.SPLITTRANSAMOUNT * c2.BASECONVRATE) from CHECKINGACCOUNT_V1 as t2
           inner join ACCOUNTLIST_V1 as a2 on a2.ACCOUNTID = t1.ACCOUNTID
           inner join CURRENCYFORMATS_V1 as c2 on c2.CURRENCYID = a2.CURRENCYID
           inner join SPLITTRANSACTIONS_V1 as split on t2.TRANSID = split.TRANSID
           where strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE)
           and strftime('%m', t2.TRANSDATE) = strftime('%m', t1.TRANSDATE)
           and split.CATEGID = cat.CATEGID
           and split.SUBCATEGID = subcat.SUBCATEGID
           and t2.STATUS <> 'V') as AMOUNT
    from CHECKINGACCOUNT_V1 as t1
    inner join ACCOUNTLIST_V1 as a1 on a1.ACCOUNTID = t1.ACCOUNTID
    inner join CURRENCYFORMATS_V1 as c1 on c1.CURRENCYID = a1.CURRENCYID
    inner join CATEGORY_V1 as cat
    on t1.CATEGID = cat.CATEGID
    inner join SUBCATEGORY_V1 as subcat
    on t1.SUBCATEGID = subcat.SUBCATEGID
    where cat.CATEGNAME = 'Food' and subcat.SUBCATEGNAME = 'Groceries'
        and t1.STATUS <> 'V'
    group by year, month
    order by year asc, month asc;

    Lua
    Code: Select all
    local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
    local labels = 'labels : [';
    local data = 'datasets : [{fillColor : "rgba(0,0,0,0.5)", strokeColor : "rgba(0,0,0,0.5)", data : [';
    local total = 0;
    -- forecast date
    local year = 0;
    local month = 0;
    -- Forecast line variables (Average)
    local average = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0};
    local count = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0};

    function handle_record(record)
        year = record:get("YEAR");
        month = record:get("MONTH");
        local date = months[tonumber(month)] .. ' ' .. year;
        labels = labels .. '"' .. date .. '",';
        record:set("DATE", date);
        local amount = string.format("%.2f", tonumber(record:get("AMOUNT")));
        if tonumber(string.sub(amount,-1)) == 0  and tonumber(string.sub(amount,-2)) ~= 0 then
            data = data .. '\'' .. amount .. '\',';
        else
            data = data .. amount .. ',';
        end
        total = total + 1;
        -- Update forecast line calculation values
        count[tonumber(month)] = count[tonumber(month)] + 1;
        average[tonumber(month)] = average[tonumber(month)] + amount;
    end

    function complete(result)
    -- Forecast line values
        data = string.sub(data,1,-2) .. ']},{fillColor:"rgba(255,0,0,0.5)",strokeColor:"rgba(255,0,0,0.5)",data:[';
        for i=1,total do
            data = data .. ',';
        end
        for i=1,12 do
            -- Label
            month = month + 1;
            if month > 12 then
                year = year + 1;
                month = 1;
            end
            local date = months[tonumber(month)] .. ' ' .. year;
            labels = labels .. '"' .. date .. '",';
            result:set('FLABEL' .. i, date);
            -- Value
            local value = "0";
            if (count[tonumber(month)] > 0) then
                value = string.format("%.2f", average[tonumber(month)] / count[tonumber(month)]);
            end
            local fval = string.format("%.2f", value);
            if tonumber(string.sub(fval,-1)) == 0  and tonumber(string.sub(fval,-2)) ~= 0 then
                data = data .. '\'' .. fval .. '\',';
            else
                data = data .. fval .. ',';
            end
            result:set('FVALUE' .. i, string.format("%.2f", value));
        end
        result:set('TREND_DATA', string.sub(labels,1,-2) .. "]," .. string.sub(data,1,-2) .. "]}]");
    end

    Template
    Code: Select all
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
    <html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <title><TMPL_VAR REPORTNAME></title>
        <STYLE><TMPL_INCLUDE NAME="master.css"></STYLE>
        <SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT>
    </head>
    <body>
        <div class="container">
            <h3>Category Trend Report</h3>
            <p><TMPL_VAR TODAY></p>
            <div class="row">
                <div class="col-xs-2"></div>
                <div class="col-xs-8">
                    <table class "table-condensed">
                    <tr><td><canvas id="reportChart" width="600" height="300"></canvas></td></tr>
                    <tr><td>&nbsp;</td></tr>
                    <tr><td><table class="table">
                    <thead>
                      <tr>
                        <th>Date</th>
                        <th class="text-right">Amount</th>
                      </tr>
                    </thead>
                    <tbody>
                        <TMPL_LOOP NAME=CONTENTS>
                        <tr>
                            <td><TMPL_VAR DATE></td>
                            <td class="money text-right"><TMPL_VAR AMOUNT></td>
                        </tr>
                        </TMPL_LOOP>
                    </tbody>
                    <tfoot>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL1></td>
                            <td class="money text-right"><TMPL_VAR FVALUE1></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL2></td>
                            <td class="money text-right"><TMPL_VAR FVALUE2></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL3></td>
                            <td class="money text-right"><TMPL_VAR FVALUE3></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL4></td>
                            <td class="money text-right"><TMPL_VAR FVALUE4></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL5></td>
                            <td class="money text-right"><TMPL_VAR FVALUE5></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL6></td>
                            <td class="money text-right"><TMPL_VAR FVALUE6></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL7></td>
                            <td class="money text-right"><TMPL_VAR FVALUE7></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL8></td>
                            <td class="money text-right"><TMPL_VAR FVALUE8></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL9></td>
                            <td class="money text-right"><TMPL_VAR FVALUE9></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL10></td>
                            <td class="money text-right"><TMPL_VAR FVALUE10></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL11></td>
                            <td class="money text-right"><TMPL_VAR FVALUE11></td>
                        </tr>
                        <tr bgcolor='rgba(255,0,0,0.5)'>
                            <td><TMPL_VAR FLABEL12></td>
                            <td class="money text-right"><TMPL_VAR FVALUE12></td>
                        </tr>
                    </tfoot>
                    </table></td></tr></table>
                </div>
                <table class="table">
                    <tbody>
                    <TMPL_LOOP ERRORS>
                        <tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
                        <tr><td><TMPL_VAR ERROR></td></tr>
                    </TMPL_LOOP>
                    </tbody>
                </table>
            </div>
        </div>   
    </body>
    <script type="text/javascript">
        <!-- Format numbers -->
        function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
        var elements= document.getElementsByClassName("money");
        for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
        <!-- Chart -->
        var data = {<TMPL_VAR "TREND_DATA">}
        var ctx = document.getElementById("reportChart").getContext("2d");
        var reportChart = new Chart(ctx).Bar(data);
      </script>
    </html>

Re: Forecast Reports

danyall
New MMEX User

Posts: 6
Joined: Mon Mar 31, 2014 9:54 am
Hello everybody!

I had imported the report but i get the following error: "could not open file for reading" After that, the program crash.

This is the version:
MMEX is using the following support products:
wxWidgets 3.0.0
SQLite3 3.8.4.1
wxSQLite3 3.0.6
Microsoft Visual Studio 1800

I think could be related with paramters ...
Can you help me?
Re: Forecast Reports
User avatar
Renato
Super MMEX User

Posts: 106
Joined: Mon Apr 25, 2011 1:36 pm
chance in Html

Code: Select all
<link href = "master.css" rel = "stylesheet">
regards Renato

use MMEX since 2009
Re: Forecast Reports

danyall
New MMEX User

Posts: 6
Joined: Mon Mar 31, 2014 9:54 am
Thank you!! It's working fine!
Re: Forecast Reports

Conrado
New MMEX User

Posts: 5
Joined: Thu Jun 26, 2014 10:47 am
Hi, when i execute the Account Forecast Report.grm shows "Block not terminated". Any advice?
Re: Forecast Reports

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Conrado wrote:Hi, when i execute the Account Forecast Report.grm shows "Block not terminated". Any advice?
No ideas, could you provide screenshot?
Re: Forecast Reports
User avatar
GabrieleV
MMEX Developer

Posts: 266
Joined: Wed Apr 09, 2014 1:45 pm
Location: Italy
viewtopic.php?f=16&t=5455

Replace style and script at the top of HTML as described and it will work.
Re: Forecast Reports

Conrado
New MMEX User

Posts: 5
Joined: Thu Jun 26, 2014 10:47 am
ScreenShot996.jpg
(6.81 KiB) Downloaded 2223 times
Nikolay wrote:
Conrado wrote:Hi, when i execute the Account Forecast Report.grm shows "Block not terminated". Any advice?
No ideas, could you provide screenshot?
Hi thanks, I all ready make it work coping and pasting the code from forum.
  • - I added the print screen with the error. This error occurs is when import the report.
  • - Replacing the code from the html it didn't work i could not find this line "<SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT>" to replaced.
Thanks Conrado
Attachments
ScreenShot996.jpg
(6.81 KiB) Downloaded 2223 times
Re: Forecast Reports

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Line 6 of template should be 
Code: Select all
<link href = "master.css" rel = "stylesheet">
Attachments
Account Forecast Report.grm
(3.02 KiB) Downloaded 244 times
Re: Forecast Reports

omalleypat
MMEX Developer

Posts: 175
Joined: Tue Jul 28, 2009 4:34 pm
Location: Atchison, KS
siena123 wrote:
  • Account Forecast Report (uses data from repeating transactions)
FYI...this report does not seem to include data from split transactions.
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest