MMEX Discussion Forum

Hi everyone,

I am trying to generate a report that will get me the current balances for my checking accounts, term accounts, investment accounts.
I think the main issue on why this code is not giving me the proper current value of my checking accounts is due to how the database is handling the "Transfer " transaction.

If the transfer is going from account A -> account B, then the transfer should be considered a withdrawal in account A and a payment in account B. This is the main constraint but when you try to query the transactions in account B, this transfer is nowhere to be seen since, as it is only marked in account A, therefore when you want to make the sum of payments and withdrawals and transfers from account B, the value will be incorrect.

Also, if the transfer is from one account to an external part (not in the accounts I setup) it should be considered a payment.

Here is a snip of the query:
--

WITH InvestmentBalances AS (
SELECT
i.ACCOUNTID,
i.ACCOUNTNAME AS InvestmentAccountName,
SUM((s.NUMSHARES * s.CURRENTPRICE) - i.INITIALBAL) AS InvestmentBalance
FROM ACCOUNTLIST_V1 AS i
INNER JOIN STOCK_V1 AS s ON i.ACCOUNTID = s.HELDAT
WHERE i.ACCOUNTTYPE = 'Investment' AND i.STATUS = 'Open'
GROUP BY i.ACCOUNTID, i.ACCOUNTNAME
HAVING InvestmentBalance >= 0
),

CheckingBalances AS (
SELECT
a.ACCOUNTID,
a.ACCOUNTNAME,
SUM(CASE WHEN c.TRANSCODE IN ('Deposit', 'Transfer') THEN c.TRANSAMOUNT
WHEN c.TRANSCODE = 'Withdrawal' THEN -c.TRANSAMOUNT
ELSE 0 END) AS CheckingBalance
FROM ACCOUNTLIST_V1 AS a
LEFT JOIN CHECKINGACCOUNT_V1 AS c ON a.ACCOUNTID = c.ACCOUNTID
WHERE a.ACCOUNTTYPE = 'Checking' AND a.STATUS = 'Open'
GROUP BY a.ACCOUNTID, a.ACCOUNTNAME
HAVING CheckingBalance >= 0
)

SELECT
'Investment' AS AccountType,
ib.InvestmentAccountName AS AccountName,
ib.InvestmentBalance AS Balance
FROM InvestmentBalances ib

UNION ALL

SELECT
'Checking' AS AccountType,
cb.ACCOUNTNAME AS AccountName,
cb.CheckingBalance AS Balance
FROM CheckingBalances cb

--

Any help would be much appreciated

Maybe this will help:

Thanks Renato! Worked like a charm!

Renato wrote: ↑Mon Oct 23, 2023 1:01 pm Maybe this will help: AccountSummary2.grm

This .grm file should be copied to the C:\Program Files\Money Manager EX\res\reports folder (on Windows OS at least)? Am I correct?

.
no, use: Tools / general Import Manager / Import
.

Great! Thanks for correcting me.

I take advantage of the post to say that if you have deleted transactions, the report does not display correctly

This is correct insofar as the deleted postings are only deactivated and can be restored.
To solve this problem, the SQL would have to be adjusted or the deleted booking would have to be permanently deleted.
.
By the way: I submitted a change request a year ago, see: https://github.com/moneymanagerex/money ... ssues/5567
.

@pdelamotar @luisdev
if you replace line 8 of AccountSummary2 with: WHERE c1.STATUS NOT IN ('V') and ifnull(c1.DELETEDTIME, '') = ''
the deleted Transactions are not added.
Procedure: Right-click on AccountSummary2, select SQL, replace line 8, save and run.