Add feature to EXPORT ALL DATA from all accounts into XLSX file, with different worksheets for each account

[rmkhalfay]

Add feature to EXPORT ALL DATA from all accounts into XLSX file, with different worksheets for each account

Originally posted by @rmkhalfay in https://github.com/moneymanagerex/moneymanagerex/issues/2513#issuecomment-657656757

[vomikan]

I've added export to JSON.

[rmkhalfay]

I have checked JSON file export; All transactions are not exported - specially those who are transferred from one account to other. It shows only in one account and not in both accounts. i.e if 100$ transferred from account A to account B. It shows entry only in account A but not in account B.

I used Excel query import from FILE, for getting JSON data

[rmkhalfay]

test.zip See zip file with test.mmb file is attached with corresponding Json file and Excel; The data is not exported properly - from bank1 to bank2 & vice versa; It has only 3 entries and json exported data gives us wrong account balances in EXCEL due to erroneous export

[vomikan]

1) My idea was represent all transactions (not accounts) as JSON file. It seems working okay.

2) I can't import json file into my Excel 2016. How to? Should I install PowerQuery?

3) May be it possible to using this idea? https://stackoverflow.com/questions/61132694/how-to-import-json-file-to-excel-without-coding

[rmkhalfay]

Sir

Many thanks for the prompt response

1. Did you see the zip file I sent. It contains one test.mmb, one json file and excel file. The excel file data is imported from json file using office 365 (not in Excel 2016). The transactions are not exported correctly for TRANSFER. In this case two transactions are required one at source and other destination account.

Also when data exported to json file from mmex it shows total number of accounts exported

2. Feature Available in office 365 or office 2019

3. Correct I used same for importing json file

Why don't you add feature to export to xlsx instead?

Best regards,

Rayees M. Khalfay

---

From: Nikolay notifications@github.com Sent: Wednesday, 22 July, 2020 12:03 pm To: moneymanagerex/moneymanagerex Cc: rmkhalfay; Mention Subject: Re: [moneymanagerex/moneymanagerex] Add feature to EXPORT ALL DATA from all accounts into XLSX file, with different worksheets for each account (#2651)

1. My idea was represent all transactions (not accounts) as JSON file. It seems working okay.

2. I can't import json file into my Excel 2016. How to? Should I install PowerQuery?

3. May be it possible to using this idea? https://stackoverflow.com/questions/61132694/how-to-import-json-file-to-excel-without-coding

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/moneymanagerex/moneymanagerex/issues/2651#issuecomment-662307566, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AMYYRVCEBSAW3HSAWEL7MU3R42MNRANCNFSM4OYUWZ3A.

[vomikan]

1) yes 2) thanks 3) OK

Export to XML seems adapted for Excel (the author not me).

[vomikan]

What the next step?

[rmkhalfay]

Sir

Please see video and txt in link

https://syntaxbytetutorials.com/import-json-data-in-excel-2016-or-2019-or-office-365-using-a-get-transform-query/

Best regards,

Rayees M. Khalfay

---

From: Nikolay notifications@github.com Sent: Wednesday, 22 July, 2020 2:22 pm To: moneymanagerex/moneymanagerex Cc: rmkhalfay; Mention Subject: Re: [moneymanagerex/moneymanagerex] Add feature to EXPORT ALL DATA from all accounts into XLSX file, with different worksheets for each account (#2651)

[image]https://user-images.githubusercontent.com/6836805/88165425-5c6db400-cc1e-11ea-83aa-4f1aeb1c0464.png What the next step?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/moneymanagerex/moneymanagerex/issues/2651#issuecomment-662372679, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AMYYRVCOOCQDTQDGSTI2JUDR424XJANCNFSM4OYUWZ3A.

[rmkhalfay]

Also current XML export is not for ALL ACCOUNTS it is one account at a time

[vomikan]

Related issue https://github.com/moneymanagerex/moneymanagerex/issues/2231

[georgeef]

In mmexutil you can find an external utility to export an mmb file to an XLSX file (I tested it on macOS, it should work also on Linux). I plan to re-implemented it in MMEX (see #6837).

May I ask, why do you need separate worksheets for each account? Once you have all transactions in one worksheet, you can apply filters, or process them as you like in Excel/LibreOffice.

I did the same with my data, with a little effort I could add formulas to derive sums and other statistics per account, at the bottom of the worksheet. The formulas are quite long (they filter transactions for each account name, they add/subtract based on the transaction type, and they take into account both the source and the destination accounts in transfers), but once you create one, you can copy-paste it for each account.

Keeping all transactions for all accounts in one worksheet has a few advantages, e.g., easier editing (no need to switch and repeat between worksheets), easier copy-paste of formulas, overview of all accounts in one place, second-order statistics across account types.

[rmkhalfay]

Thanks for the response. Yes one worksheet with all data is also fine. Can you please guide me how to install mmexutil. I clicked on it and it shows installation required for cpan?

[georgeef]

Perl (https://www.perl.org) is a well-known programming language, available for all major operating systems, and cpan is the default package manager for Perl.

The mmexutil installation instructions are based on how I installed it on macOS from terminal. Installation on any other Unix environment (e.g., Linux, WSL on Windows, Cygwin on Windows) is similar. If you use Windows without any Unix environment, in principle you need to install Perl and then the required packages listed in the instructions. It should be fairly easy to find instructions in the internet, but if you never did programming it will be challenging.