moneymanagerex / general-reports Goto Github PK

View Code? Open in Web Editor NEW

71.0 27.0 47.0 664 KB

Bunch of general reports for Money Manager Ex

Home Page: http://moneymanagerex.org/

License: MIT License

Lua 94.93% Python 2.78% Go 0.91% CMake 1.38%

sql lua html data-visualization report graph chart

general-reports's Introduction

general-reports

General reports for Money Manager Ex which can be downloaded and imported easily. Reports can be created using Money Manager Ex as well.

Report structure

Typically, one general report contains:

sqlcontent.sql (MMEX will execute this SQL first to return one result set)
```
select * from assets;
```

luacontent.lua (There are two APIs here)

handle_record

function handle_record(record)
  -- Your logic to modify a record and apply this function against every record from SQL.
  record:set("extra_value", record::get("VALUE") * 2);
end

complete

function complete(result)
  -- Put some accumulated value and apply this function after SQL completes.
  result:set("TOTAL", 1000);
end

template.htt (a plain text template file powered by html template which shares the same syntax with Perl's HTML::Template)

Contributing

Fork the repository
Create your feature branch (git checkout -b my-new-feature)
Commit your changes (git commit -am 'Add some feature')
Push to the branch (git push origin my-new-feature)
Create a new pull request

Donations

Star it
Donate to my Ripple address: rUY7DvWqNnSYCYiVr986W71tuaKtDCMNz3
PayPal

general-reports's People

Contributors

Stargazers

Watchers

general-reports's Issues

Account Summary Report

Hello I just managed to update my database to version 13, in order to be able to use Vomikan's modified version of the Account Summary report, so that i can print it for a specific date. Its the file contained in this issue thread: #42

After changing all references to tables with "_V1" by removing the V1 (none of my tables has V1 in the name) and all references to currencies which was causing the report to fail (I only use euros), I managed to get the report to run, and return two columns - the account name and the balance.

However, in some cases, actually on every other line, rather than get a balance to 2 decimal places i get a balance of many decimal places like, 14.7599999999899 (instead of 14.76) OR 5.51381162949838e-12 instead of 0.00

The graph does not work either , but that does not bother me. I just need to be able to enter a specific date and know what the balance was on each of my accounts.

Here is what my edit SQL code says. I dont know anything about SQL, so i would be grateful if someone could help out.

with b as (select ACCOUNTID, STATUS, TRANSDATE,
            (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
        from CHECKINGACCOUNT
        union all
        select TOACCOUNTID, STATUS, TRANSDATE, TOTRANSAMOUNT 
        from CHECKINGACCOUNT
        where TRANSCODE = 'Transfer') 
select a.ACCOUNTNAME, 
    total(b.TRANSAMOUNT) + a.INITIALBAL as Balance 
from ACCOUNTLIST as a, b
inner join CURRENCYFORMATS as c on c.CURRENCYID = a.CURRENCYID
where a.ACCOUNTTYPE  not in ('Investment') 
and a.STATUS = 'Open' and b.STATUS <>'V'
and a.ACCOUNTID=b.ACCOUNTID
and b.TRANSDATE <='&single_date'
group by a.ACCOUNTNAME
order by a.ACCOUNTNAME asc;

Thanks for any help you can provide guys!

Add installation instructions to readme doc

Please add installation instructions to readme doc that describe how you install these reports on a MMX Desktop.

translation script

To avoid exception in some cases like this

The function should be modified to somthing like this:

    function _(n) {
	    var my_lang = <TMPL_VAR LANGUAGE>; -- not working !!!
        if (my_lang == null) 
		    my_lang = translations.english;
		else
            my_lang = translations.my_lang;
        var item = my_lang[0][n];
        var out = (item == null) ? n : item;
        return out;
    }

New Category report

Regarding this issue I've created GRM adapted report
moneymanagerex/moneymanagerex#1689

Categories_v4.zip

REPORTS - On all charts

Hi everyone,

in fact this is not a bug but only a request. I don't think it's difficult since we have all the data. The request concerns the addition of "values" such as "Percentage" and "absolute value" inherent in the graph. This is to allow, for example, to print only the graph without the data, leaving only the totals. The reason is to highlight the values when in the graph we have many "slices / sections" that hide the values.

Maybe you can put it in version 1.6.4 new.

Another example

SMA200 SMA50 SMA10

I've creared SQL query for calculate (Simple Moving Average) SMA200 50 10 for AAPL

WITH d as (
WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<365)
SELECT 
'AAPL' s
, x 
, date('now', '-'||x||' days') d
FROM cnt)
select d.x
, d.d date
, case when d.d= date(d.d, 'start of month') then d.d else '' end label
, (select value from STOCKHISTORY where DATE = date(d.d)  and SYMBOL = d.s  ) Value_
, (select value from STOCKHISTORY where DATE >= date(d.d, '-3 days') and DATE <= date(d.d)  and SYMBOL = d.s and value is not null order by 1 desc limit 1 ) Value
, (select avg(value)  from STOCKHISTORY where DATE <= d.d and DATE > date(d.d, '-50 days') and SYMBOL = d.s) avg50
, (select avg(value)  from STOCKHISTORY where DATE <= d.d and DATE > date(d.d, '-200 days') and SYMBOL = d.s) avg200
 from d 
where x < 100

I need graph for this data like this one:

https://marketrealist.com/2019/10/amzn-aapl-msft-tech-stocks-averages-pre-earnings/

Missing file: template.htt

CMakeLists.txt, check_gm.py and pack_gm.py contais reference to general-reports/packages/Account/AccountBalances/template.htt file, which not exists.

Where is new scheme graph?

https://forum.moneymanagerex.org/viewtopic.php?f=16&t=8015

Accounts balance for any date

http://forum.moneymanagerex.org/viewtopic.php?f=23&t=6952

Extended Trial Balance

Hi there

I wonder if there is a report for an extended trial balance available?

I have managed to find an income and expense report with breakdowns as well as account balance reports with breakdown.

This is the last thing I am looking for to complete my set of accounts.

attachments href

I created my own report with the custom report manager.

Using the old "alldata" view and added
left join attachment att on att.REFID=cans.transid
to get ATTACHEMENT.Filename

HTML
a href="../MMEX_privat_Attachments/Transaction/<TMPL_VAR Filename>

In 1.4 the PDF is shown in MMEX-Window

In 1.3 I get an error

No access to page

• Make sure the web address //ieframe.dll/dnserrordiagoff.htm# is correct.
• Find this website on Bing
•Refresh page

This report will show account balances for a specific date.

When I run the report, for example, as of May 31, 2024, I got information related to transaccions registered not later than that date. It means, I guess, the report considered the date of the column "Last Update". In relate with this periodo, I have updaloaded transactions during June with effective date in May.
And this registrations are not summed in May.

Update to use ApexCharts

Update GRM charts to use ApexCharts so that we can retire ChartNew.js from the main package. It will still be possible to use ChartNew.js in user developed scripts but the user would need to pull in the javascript library from external source.

Invalid end_date value for 'Over Time' period

end_date for the query is null if &end_date' = '9999-12-31' (Over Time period)

WITH PeriodSelection as (
     select date('&begin_date', 'start of month') begin_date
         , date('&end_date', 'start of month', '+1 month', '-1 day') end_date
 )

Please, replace by

WITH PeriodSelection as (
     select date('&begin_date', 'start of month') begin_date
         IIF(date('&end_date') > date('9999-12-30') , date('9999-12-31'),  date('&end_date', 'start of month', '+1 month', '-1 day') )  end_date
 )

or change max value of period.

Report quality survey

Many MMEX reports are broken. They support only the core features of MMEX ignoring many new functions like multi-currency, shares, transfers etc. Wrong results will be presented t user when if such report will be used.

My proposal is to prepare a checklist for calculating report quality. All questions can be answered yes, supported or no, broken at this element or n/a for this report.

@moneymanagerex/desktop-mmex-team @moneymanagerex/general-report-team Could you commend or complement list below?

Full financial year transaction and summary report

Just switched to a Mac having always used Windows and Microsoft Money to manage finances.

At the end of my financial year I always printed a transaction report (list of all income and expenses transactions) grouped by category, with totals for each category and grand totals at the end.

Is there a similar report for MMEX please, I don't see one like it displayed on the 'Reports' web page at GitHub

https://forum.moneymanagerex.org/viewtopic.php?f=16&t=10341

need to change query for Expense and Revenue report

I've made an mistake with use of totransamount in "Expense and Revenue" report due to a lack of understanding.
I think there is no impact on result but it's not the good way.

i will make a new pull request soon in order to correct it

Report to forecast near term cash flow

I had a desire for a report to forecast my cash flow for the next month. The Upcoming Transactions pane on the MMEX home page does not forecast far enough ahead for my desires. The built-in Cash Flow report when the Monthly option is selected gives you the aggregate information but I wanted detail lines as well as the total. I hope other MMEX users find this helpful. And I would appreciate your telling me if you find any errors. Thank you very much.

SELECT TRANSDATE, b.PAYEENAME AS PAYEES, case when TRANSCODE = 'Deposit' then TRANSAMOUNT else NULL end AS CREDITS, case when TRANSCODE = 'Withdrawal' then -TRANSAMOUNT else NULL end AS DEBITS FROM BILLSDEPOSITS_V1 a join payee_v1 b USING(PAYEEID)
WHERE TRANSDATE < date('now','+32 days') 
UNION ALL
SELECT date('now','+32 days') AS TRANSDATE, ' '  AS PAYEES, NULL AS CREDITS, ' ' AS DEBITS 
UNION ALL
SELECT date('now','+32 days')  AS TRANSDATE, ' ' AS PAYEES, NULL AS CREDITS, ' ' AS DEBITS 
UNION ALL
SELECT  date('now', '+32 days' ) AS TRANSDATE, 'TOTAL NEAR TERM EXPENSES' AS PAYEES, NULL AS CREDITS, sum(case when TRANSCODE = 'Withdrawal' then -TRANSAMOUNT else 0 end )  AS DEBITS FROM BILLSDEPOSITS_V1 WHERE TRANSDATE < date('now','+32 days') 
UNION ALL
SELECT date('now','+32 days' ) AS TRANSDATE, 'TOTAL NEAR TERM INCOME' AS PAYEES, sum(case when TRANSCODE = 'Deposit' then TRANSAMOUNT else 0 end )  AS CREDITS, NULL AS DEBITS FROM BILLSDEPOSITS_V1 WHERE TRANSDATE < date('now','+32 days')  ORDER BY TRANSDATE

https://forum.moneymanagerex.org/viewtopic.php?f=16&t=9580

Reports to compare analogous period from current month with X previous months

SQL stage 1

with tr as (
    select 
         strftime('%m', TRANSDATE) as month
         , strftime('%Y', TRANSDATE) as Year
 	 , sum((case c.categid when -1 then  splittransamount else  transamount  end) 
	        * cf.BaseConvRate
         ) amount
    from checkingaccount_v1 c
    left join splittransactions_v1 s on s.transid=c.transid
    left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID=c.ACCOUNTID
    left join currencyformats_v1 cf on cf.currencyid=AC.currencyid
    where transcode = 'Withdrawal'
    and c.status !='V'
    and ac.status !='Closed'
    and (date('now', 'start of month','-35 month','localtime') <= transdate
        and transdate < date('now', 'start of month','+1 month','localtime'))
    and strftime('%d', (date('now', 'localtime'))<=  strftime('%d', TRANSDATE)
        and transdate < date('now', 'start of month','+1 month','localtime'))
    group by month, year)
, d as (select '01' as month, 1 as ID
union select '02', 2
union select '03', 3
union select '04', 4
union select '05', 5
union select '06', 6
union select '07', 7
union select '08', 8
union select '09', 9
union select '10', 10
union select '11', 11
union select '12', 12
order by ID
)
select d.ID
,ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','localtime'))), 0) as "year"
, ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','-12 month','localtime'))), 0) as "year-1"
, ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','-24 month','localtime'))),0) as "year-2"
from d

Report versioning framework

Problem description

There are users of different MMEX versions. All of them wants to download new reports for their MMEX installations. We must supply working reports to users.
This is complicated with changes introduced in new DB schemas and new MMEX features implemented (like two-letter separate statuses for transfers). SQL queries must be updated to work with newer MMEX versions and executing GRM report against wrong DB version will not work.

Requirement

We must do report versioning and tag them with compatible DB schema version.

Proposal

Store GRM reports inside following JSON structure:

{
  "title": "New report",
  "description": "bla bla bla...",
  "version": 5,
  "database": {
    "min": 13,
    "max": 17
  },
  "sql": "SELECT * FROM ...",
  "lua": "local ...",
  "template": "<html>..."
}

Account forecast beyond 12 months?

Hi!

I am interested by the account forecast but it would be really useful it we could define it for 24 months or 36 months. The reason behind this is that typically we have expenses that can be important once a year (ex: holidays, vacations, housing taxes or income taxes). Having forecasts that is restricted to 6 months or 12 months donèt allow us to see the reall trends going on as it will inevitably misses the recurring yearly vacation or yearly housing taxes.

I did tried to add more events but it seems that beyond 12 months the script no longer returns correct values.

Can you help out here? Thanks a lot!

Report CategoryForecast no longer including subcategories db v18

In v1.6.1, the CategoryForecast report would return totals for 'Bills' and all subcategories e.x. Bills:Telephone, Bills:Credit Card etc. Now it returns only items marked bills without subcategory. SQL should be modified to return all subcategories that have 'Bills' category as oldest ancestor.
@n-stein ?

Refactoring for DB ver 17

#5563

memory prefix

Report with this header is working fine on my Windows PC.

Graph values not JSON format

https://github.com/moneymanagerex/general-reports/tree/master/packages/AccountSummary

The following reports does not work on mmex v1.2.0+

BudgetMonitoringCurrentMonth
BudgetMonitoringLastMonth
CategoriesStatLast12Months
CategoryForecast
CategoryTrend

The charts are created using the javascript framework ChartJS from
http://chartjs.devexpress.com/, which creates simply
stunning gauge widgets.
and copy the following three files from the archive directory Lib/js directly
into your MoneyManagerEx/res
dx.chartjs.
jquery-2.0.3.min.
globalize.min.js

The data don't matching

I liked the report: Expense and Revenue report by Year. But this report don't matching with the default program report (receive vs expenses - current year).
Please, look the attached files to check.
Maybe, the problem is me. But if so.. please help me.
Thank you.

@moneymanagerex/general-report-team Do we need preserve old copy for MMEX <1.4.0 users?

chart of my monthly income of every month

Hi! I love this software (Windows version) but there´s one thing that seems to be missing (or I haven´t found it): I´d like to see a chart of my monthly income of every month, but I can only see the current month´s balance and last month´s balance. How can I see every month´s balance so I can see how much money I made each month to compare?

https://www.facebook.com/MoneyManagerEx/inbox/?mailbox_id=242286559144586&selected_item_id=100008031784151

pacman repo appears to have out of date version

It looks like the pacman repo still has version 1.3.3-3, while the website shows 1.3.5 as the stable version