Current Balance of my Accounts

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
SPINTO
New MMEX User
Posts: 4
Joined: Sun Feb 12, 2023 4:09 pm
Are you a spam bot?: No

Current Balance of my Accounts

Post by SPINTO »

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 :)
User avatar
Renato
MVP MMEX User
Posts: 645
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Current Balance of my Accounts

Post by Renato »

Maybe this will help:
AccountSummary2.grm
(2.71 KiB) Downloaded 215 times
Renato Forum Administrator
SPINTO
New MMEX User
Posts: 4
Joined: Sun Feb 12, 2023 4:09 pm
Are you a spam bot?: No

Re: Current Balance of my Accounts

Post by SPINTO »

Thanks Renato! Worked like a charm! :D
luisdev
Senior MMEX User
Posts: 40
Joined: Tue Oct 06, 2015 6:51 pm
Are you a spam bot?: No

Re: Current Balance of my Accounts

Post by luisdev »

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?
User avatar
Renato
MVP MMEX User
Posts: 645
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Current Balance of my Accounts

Post by Renato »

.
no, use: Tools / general Import Manager / Import
.
MMEX04.png
MMEX04.png (49.92 KiB) Viewed 69202 times
Renato Forum Administrator
luisdev
Senior MMEX User
Posts: 40
Joined: Tue Oct 06, 2015 6:51 pm
Are you a spam bot?: No

Re: Current Balance of my Accounts

Post by luisdev »

Great! Thanks for correcting me.
pdelamotar
Senior MMEX User
Posts: 22
Joined: Sun Nov 13, 2022 5:49 am
Are you a spam bot?: No

Re: Current Balance of my Accounts

Post by pdelamotar »

I take advantage of the post to say that if you have deleted transactions, the report does not display correctly
DELAMOTA
User avatar
Renato
MVP MMEX User
Posts: 645
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Current Balance of my Accounts

Post by Renato »

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
.
MMEX06.png
MMEX06.png (52.97 KiB) Viewed 68345 times
MMEX07.png
MMEX07.png (37.1 KiB) Viewed 68345 times
Renato Forum Administrator
User avatar
Renato
MVP MMEX User
Posts: 645
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Current Balance of my Accounts

Post by Renato »

@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.
Renato Forum Administrator
Post Reply