Page 1 of 1
Compare Two Periods Report
Posted: Fri Dec 02, 2022 12:10 pm
by gpiantini
How can I get a compare between for example January-March 2021 and January-March 2022 in the same report?
I would like to see all my categories or all my beneficiaries compared between the two periods.
Thanks a lot for your help.
Re: Compare Two Periods Report
Posted: Fri Dec 02, 2022 2:35 pm
by MartinArmstrong
I find it easiest and more flexible to handle tasks like this outside of MMEX.
As an example, for Reports | Categories | Monthly:
1. Display the report for the first year you are interested in.
2. Set the Chart dropdown to Hide.
3. Right-click in the report and 'Select all' (or press CTRL+A)
4. Press CTRL+C to copy the report data
5. In Excel (or LibreOffice Calc or Google Sheets) press CTRL+V to paste the data.
6. Repeat for the second year, pasting below the first year's data, beside it, or on another worksheet depending on what you are trying to achieve.
7. Compare the data visually, or set up formulae or conditional formatting to compare it in the way you want (for example, highlighting if >10% increase in spending for a category)
Re: Compare Two Periods Report
Posted: Fri Dec 02, 2022 5:06 pm
by gpiantini
Thank you so much for your help!
I will try for this solution and let you know about it.
Re: Compare Two Periods Report
Posted: Fri Dec 02, 2022 7:59 pm
by gpiantini
MartinArmstrong wrote: ↑Fri Dec 02, 2022 2:35 pm
I find it easiest and more flexible to handle tasks like this outside of MMEX.
As an example, for Reports | Categories | Monthly:
1. Display the report for the first year you are interested in.
2. Set the Chart dropdown to Hide.
3. Right-click in the report and 'Select all' (or press CTRL+A)
4. Press CTRL+C to copy the report data
5. In Excel (or LibreOffice Calc or Google Sheets) press CTRL+V to paste the data.
6. Repeat for the second year, pasting below the first year's data, beside it, or on another worksheet depending on what you are trying to achieve.
7. Compare the data visually, or set up formulae or conditional formatting to compare it in the way you want (for example, highlighting if >10% increase in spending for a category)
I tried your solution, but thers is no way to create a Report inside MMEX?
Can someone help me to create the code for this kind of report?
Re: Compare Two Periods Report
Posted: Fri Dec 02, 2022 11:45 pm
by MartinArmstrong
You wrote: "I would like to see all my categories or all my beneficiaries compared between the two periods"
But you haven't said:
1. Which report(s) you want to actually want to run the comparison on. I just guessed at Reports | Categories | Monthly.
2. How exactly you would want to see the comparison presented for the two time periods? Please could you give an clear example, otherwise people are just guessing what you need.
Re: Compare Two Periods Report
Posted: Sat Dec 03, 2022 8:56 am
by gpiantini
MartinArmstrong wrote: ↑Fri Dec 02, 2022 11:45 pm
You wrote: "I would like to see all my categories or all my beneficiaries compared between the two periods"
But you haven't said:
1. Which report(s) you want to actually want to run the comparison on. I just guessed at Reports | Categories | Monthly.
2. How
exactly you would want to see the comparison presented for the two time periods? Please could you give an clear example, otherwise people are just guessing what you need.
Yes, you're right I'm sorry.
I post here an example of what I'd need to see in the report inside MMEX.
I would be able to select the periods to compare and select each category or beneficiary I need in the moment i Run the report script.
It has to be a bit flexible, to permit me to compare some differents periods with selected category, all of them, or only some beneficiaries.
Thanks for your time and your help.
- Example.jpg (32.3 KiB) Viewed 7703 times
Re: Compare Two Periods Report
Posted: Sat Dec 03, 2022 11:52 am
by MartinArmstrong
1. Is it just Expenses that you are interested in, or is Income vs. Category relevant as well?
2. In your requirement "select each category or beneficiary", do you mean "each category or payee"? If not payee, what do you mean by beneficiary please?
3. In your requirement "select each category or beneficiary", do you mean that you want two different reports; one for categories and another for 'beneficiaries'?
Re: Compare Two Periods Report
Posted: Sat Dec 03, 2022 4:33 pm
by gpiantini
MartinArmstrong wrote: ↑Sat Dec 03, 2022 11:52 am
1. Is it just Expenses that you are interested in, or is Income vs. Category relevant as well?
2. In your requirement
"select each category or beneficiary", do you mean "each category or payee"? If not payee, what do you mean by beneficiary please?
3. In your requirement
"select each category or beneficiary", do you mean that you want two different reports; one for categories and another for 'beneficiaries'?
Martin thank you for your questions about it, I try to explain point per point:
1. No, I just to analyze and compare all the category expenses in two different periods (ex. "01/01/2021-31/03/2021" vs "01/01/2022-31/03/2022")
I would be able to choose every period I need in that moment.
2. I need to be able if I need a Category Compare Report or Payee Compare Report, always based on two different periods as before.
3. Yes, I would be able to to this, not necessary in same time/report.
I hope I clarified your doubts about it.
Thanks a lot for your help.
Re: Compare Two Periods Report
Posted: Sat Dec 03, 2022 8:16 pm
by Renato
My proposal would be:
In Excel, read the entries from the database .mmb and create the evaluations with the pivot functions.
Re: Compare Two Periods Report
Posted: Sun Dec 04, 2022 10:27 am
by gpiantini
Renato wrote: ↑Sat Dec 03, 2022 8:16 pm
My proposal would be:
In Excel, read the entries from the database .mmb and create the evaluations with the pivot functions.
I don't know how to do this.. if you can help me but I prefere generate a report directly into MMEX.
Re: Compare Two Periods Report
Posted: Sun Dec 04, 2022 1:15 pm
by Renato
Renato wrote: ↑Sat Dec 03, 2022 8:16 pm
I prefere generate a report directly into MMEX.
No such evaluation is provided, but you can submit a request at
https://github.com/moneymanagerex/money ... +is%3Aopen.
Due to the many number of requests and little programming capacity it will take some time.
Information to PIvot, you can read:
https://support.microsoft.com/en-us/off ... 9134456576
Note: Unfortunately, the CTRL+c function does not work in the "All transactions" report, otherwise it would be possible to transfer the selected transactions to Excel in a simple way. See:
https://github.com/moneymanagerex/money ... ssues/5394
Re: Compare Two Periods Report
Posted: Sun Dec 04, 2022 3:43 pm
by MartinArmstrong
@Renato, as a workaround you can do this:
1. Go to the All Transactions view.
2. File | Export | HTML Files and save the file (
xxx.html)
3. Open
xxx.html in Excel, or just drag it into an open worksheet. You now have the transaction data, as you would have if you did copy/paste.
Re: Compare Two Periods Report
Posted: Sun Dec 04, 2022 4:09 pm
by Renato
Hi @MartinArmstrong
thanks for the tip, but the amounts > 999.99 are displayed with a thousand delimitation "2 000 000.00" and Excel cannot calculate with that.
Re: Compare Two Periods Report
Posted: Sun Dec 04, 2022 6:32 pm
by MartinArmstrong
Renato wrote: ↑Sun Dec 04, 2022 4:09 pm
the amounts > 999.99 are displayed with a thousand delimitation "2 000 000.00" and Excel cannot calculate with that.
I don't have that problem because my thousands separator in Windows
and in MMEX is a comma.
- excel_cjoDozss90.png (54.88 KiB) Viewed 11704 times
If you want to use a space thousands separator in Excel (while keeping your Windows thousands separator in other applications), then in Excel:
- Click File > Options
- Click Advanced
- Under Editing Options, clear the check box Use system separators
- Click in the Thousands separator box
- Enter a space
- Click OK
- ZWk11YhV3I.png (120.37 KiB) Viewed 11704 times
Re: Compare Two Periods Report
Posted: Sun Dec 04, 2022 7:24 pm
by Renato
Hi @MartinArmstrong
Thank you very much, I did not know, has worked perfectly.
Re: Compare Two Periods Report
Posted: Wed Sep 27, 2023 4:06 pm
by Noelok
MartinArmstrong wrote: ↑Sun Dec 04, 2022 6:32 pm
Renato wrote: ↑Sun Dec 04, 2022 4:09 pm
the amounts > 999.99 are displayed with a thousand delimitation "2 000 000.00" and Excel cannot calculate with that.
I don't have that problem because my thousands separator in Windows
and in MMEX is a comma.
Hi - How did you get MMEX to use comma as thousands separator? That would help me a lot!