Reconcile "As of" date for all account types
When reconciling a "Bank" account, the Reconcile Account dialog has an (optional) As of date.
I suggest this be available for all accounts, regardless of type.
I don't currently have any loan accounts (Moneydance imported my mortgage as a liability and “loan” isn’t offered as an account type to change it to), so I don't know whether that option appears for those, but it is definitely not present for credit card, investment, asset, or liability accounts.
I get statements for credit cards and investments, so they are reconciled as of the statement date.
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
1 Posted by dwg on 14 Jul, 2024 01:25 AM
The "As of" date is only enabled if you download or import transactions for that account using OFX. The OFX files is expected to have a <DTASOF> value in the file. If this box is selected, then all that happens is that transactions that were downloaded/imported on or before the date chosen will be marked as reconciling. I'm not aware if the Moneydance+ data stream has an equivalent.
Clearly any transaction downloaded and imported is in Moneydance and will be on the statement if it is prior to the DTASOF value so marking it as reconciling automatically is a safe thing to do.
2 Posted by Stuart Beesley ... on 14 Jul, 2024 06:37 AM
… identical for md+
As it stands, it would be pointless for non downloaded / manual txns. Or rather, prone to errors…
3 Posted by steven_geraghty on 15 Jul, 2024 01:37 PM
I know the OFX downloads have a <DTASOF> value.
That’s actually my issue – accounts that I can get OFX downloads for already have the statement as of date, non-OFX accounts do not, and I can’t provide one.
Neither my mortgage nor any of my credit cards provide OFX downloads, so that’s not an option.
I disagree that it would be pointless to enter the as of date for non-downloaded / manual txns. I reconcile those account exactly the same way that I do the accounts that do support OFX downloads. When I get a monthly statement, I reconcile my account(s), and the as of date is the statement end date.
I can’t think of any reason it would be prone to errors.
All transactions have a date. Transactions dated on or prior to the as of date could be marked as reconciled, the same as they are for OFX downloaded trx. Seems pretty straightforward to me – I can’t think of any reason it should be any more prone to errors than with accounts that are OFX downloaded.
If you mean errors in the manually entered transactions, that’s the whole point of reconciliation – to verify that my records (register) match the bank’s records (statement)
Automatic reconciliation aside (that’s not what I really want/need), here’s what I am actually after:
I want to be able to see, somewhere in Moneydance, the last statement date (reconciled as of date) and, ideally, the last statement (reconciled) balance. The end statement balance is already a field in the first reconcile dialog regardless of OFX status, so that is certainly possible.
A couple options for where reconcile as of date/balance might be made visible:
1) In the Account/Edit Account view.
2) in the Account Balances report.
3) My last choice: at the bottom of the register (in addition to the current balance already displayed there).
Bonus points if statement date/balance are included in QIF exports.
The QIF standard has indicators “$” for statement balance and “/” for statement balance date
(to be fair, I’ve exported QIF files from multiple Quicken versions, and none of them ever exported these fields)
Toolbox and Extract Data extensions could, at the author’s discretion, also include the statement date/balance.
4 Posted by steven_geraghty on 15 Jul, 2024 01:47 PM
Correction: the accounts I download via OFX have AN as of date - it is not necessarily the statement as of date.
5 Posted by dwg on 15 Jul, 2024 09:54 PM
The as of date in downloads is the date that the download has transactions up to.
6 Posted by steven_geraghty on 15 Jul, 2024 10:03 PM
Yes, I know what the as of date in OFX downloads is.
7 Posted by dwg on 15 Jul, 2024 11:12 PM
So what you basic want is the reconciliation date and the statement balance.
Some months ago there was a discussion with the developer around the issue of reconciliation, generating reconciliation reports at various dates etc.
These are things that cannot be done today because there is just not the data present, some items are just there at a point in time.
The capability that was being presented was that a new reconciliation object would be created each time a reconciliation was performed that would record all the needed information in it, this would naturally include the statement balance date and the end statement value.
Given the other development work that has been occurring I do not think this would have progressed past the conceptual stage at this time.
8 Posted by steven_geraghty on 16 Jul, 2024 01:13 AM
Yes, I just want to add the statement date for non-OFX accounts.
Given that both OFX & non-OFX reconciliation display the beginning balance and prompt for the ending balance, I assumed it wouldn't be difficult to add the statement date for non-OFX.
Apparently, it's far more complicated than I'd have expected.
Thanks for the reply.
9 Posted by dwg on 16 Jul, 2024 03:09 AM
The beginning balance is calculated at run time so it is not saved anywhere.
The as of date is part of any OFX/QFX/Moneydance+ download, and does not related to the statement date as you are aware. This date is not present in any QIF/CSV downloads, which are just the transactions. The statement date you do not enter, it is not present in the file.
As I said the As of date is just used to automatically mark transactions as reconciling during the reconciliation process.
The ending balance that you enter is just used during the reconciliation to allow calculation of the "differences" value during the reconciliation, it is currently not saved.
The driver for change will be the limited ability around reconciliation reporting which is something that regularly gets raised. Mostly this is the desire to be able to generate a report for older periods, as well as a report immediately following a reconciliation, which cannot be done today. To be able to do this will require the capture and saving of more information when the reconciliation is being performed. At the moment much of the information is transient.
The developer has recognized the limitations that currently exist.
10 Posted by steven_geraghty on 16 Jul, 2024 02:54 PM
I assumed the beginning balance was calculated (seems like the obvious way to do it).
I actually do enter the statement date (for accounts I can download OFX files for). The dialog defaults to the last OFX as of date, but I can change it.
The attached png shows what I get when I hit ctrl-b on my checking account. (balances redacted)
The as of date "7/11/2024" is the date of my last OFX download.
I understand how the end balance and date are derived and used; I just can't quite grasp why I shouldn't be able to set an as of date (even if it's only used to mark transactions and not stored) regardless of OFX availability.
The end balance for OFX accounts defaults to the current (trx up to & including today) register balance, not the balance as of the last statement date or last OFX <BALAMT>.
This is, of course, perfectly reasonable and what I would expect.
Personally, I wouldn't have a need for reporting on older periods, but I would kind of like to be able to see the LAST date an account was reconciled (the as of date, not the date the recon was performed).
I appreciate your time and explanations.
11 Posted by dwg on 16 Jul, 2024 09:50 PM
I believe the reason for only exposing the As of date when only OFX and Moneydance+ is being used is because of the process used and the data expectation. With these process you get an As of data as part of the download, you also use the confirmation process to effectively accept them, as well as the merge process that is part of this. So these transactions are known to be in the register and also known to be on the bank statement.
At the other end of the spectrum you have manual entry. To allow automatic reconciliation marking of all transactions before an entered date opens the possibility of errors, it could mark a transaction that is wrong or has even been entered into the wrong account for example, not really desirable. You could argue that it should be allowed for QIF and CSV imports, but you are in a grey area there as there is no date in the download, and the software is being conservative by not allowing for values like dates that have human involvement. We humans are the worst error producers around :)
Moneydance takes a conservative approach to managing finances so generally does not do things that are potentially error prone.
12 Posted by Stuart Beesley ... on 25 Jul, 2024 03:10 PM
Ref: "Toolbox and Extract Data extensions could, at the author’s discretion, also include the statement date/balance.".. I've thought about this. Comments as follows.
1/ The only place to put such data in extract_data would be the extract account registers / txns.. But these are for txns and it would not be right to pollute this extract with repeating account summary information.
2/ Toolbox can already show you all the data that is stored in MD.
3/ Moneydance only stores some info...:
getOnlineAvailBalance() - with my ofx this was zero
getOnlineAvailBalanceDate() - with my ofx this was not set
getOnlineLedgerBalance() - set by my ofx
getOnlineLedgerBalanceDate() - set by my ofx
getMostRecentTxnUpdate() - calculated. The last date used by a ofx txn
There is NO stored reconciliation data... I know the developer is considering this.
The fields above that are set can be seen in the first screen of Reconciliation Window.
Not all OFX providers set all fields.
I don't think there is much at this time I could do/extract/show to help you more...