Check# content is for user reference only; doesn't affect calculations?
True or false? If false, what effect does the content have on the operation of the program?
I infer that it's true because the user can change the content arbitrarily. (The article drops the "#" from the field name, but the software always displays it.)
Comments are currently closed for this discussion. You can start a new one.
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 Stuart Beesley ... on 29 Mar, 2023 07:56 PM
.
2 Posted by brec on 29 Mar, 2023 09:53 PM
OK. But in a .qif file the same field code,
N, is used both for Check# for bank transactions and Action for investment transactions. (Reference -- there's no formal or authorized spec for QIF, but this article is the most complete that I've found.)The exported .qif I'm using puts Action codes in investment
Nfields and I'm pretty sure MD is ignoring them. For example, for an EFT transfer from a bank to a brokerage, the .qif hasNTXFRfor the bank withdrawal, with amount negative, andNXINfor the brokerage deposit. MD gets it right, albeit in the bank's register it hasTXFRin the Check# field with the amount in the Payment column, and in the brokerage's Bank Register is has[XFER]in in the Check# field with the amount in the Increase column.The bank transactions precede the brokerage transactions in the .qif, and I assume that the
[XFER]was created by MD -- indicated by the square brackets -- and that MD accordingly ignored the corresponding brokerage transaction it later encountered in the .qif. I believe that MD defaults to withdrawal when it doesn't have an investment Action specification, and it subtracts the amount from the previous balance -- effectively adding it when the amount is negative. So this transaction worked only because the bank side was processed first.But ignoring the
Nfield for investment accounts will cause a problem withNWITHDtransactions having a negative amount in the .qif. MD, although ignoring theWITHDwill, even though it assumes a withdrawal, assumes the amount is positive and subtract the (negative) amount from the previous balance.This is all hypothesizing from what I've seen. Writing this kind of message helps me clarify(?) my thoughts. Thanks for reading.
EDIT: Just saw a bank xaction that goes against my theory.
NEFTfor a transfer received from a brokerage.EFTseems completely ambiguous as to direction, but MD correctly handled it as a deposit. On the brokerage side the Bank Register Check# is[EFT], correctly handled as a withdrawal.3 Posted by dwg on 30 Mar, 2023 12:19 AM
I've attached the latest version of the QIF spec I have. As far as I know there have not been any changes since this was published, Intuit have been trying to kill off QIF for years so are unlikely to have expanded it.
4 Posted by dwg on 30 Mar, 2023 12:31 AM
Moneydance does not ignore the N option.
In non Investment accounts it is just a text string. In this accounts Money is either going into the account or going out of the account and this is determined by the amount sign.
In Investment accounts the N is an action and determines how transactions are to be processed by the software. The import thing to note is there is only a limited number of valid actions
The valid action types in Moneydance are:
The possible Action types are:
Buy: Purchase shares of a security, or units in an investment from funds in the account.
BuyXfr: Purchase shares of a security, or units in an investment using funds from another account.
Sell: Sell shares of a security, or units in an investment and deposit funds into the account.
SellXfr: Sell shares of a security, or units in an investment, placing the funds into another account.
Div: Dividends or any income earned for a specific security, funds are deposited into the account. In general this is the action to use for any regular income from an investment, with an appropriate category used. The name of “Div” is used for compatibility.
DivReinvest: Dividends or income earned for a specific security are used to purchase more shares of that security. In general the action for any regular income from an investment, that is used to buy further units in that investment with an appropriate category used.
DivXfr: Dividends or income earned for a specific security that are transferred to another account. In general the action for any regular income from an investment, where the funds received are transferred to another account with an appropriate category used.
Short*: Sell shares of a security that you do not own, but can borrow (often from your broker), in the anticipation that those shares will go down in value and can be purchased for less in the future in order to return them to the lender. In the register the Short sale works and looks the same as a Sell transaction. This type of transaction is often referred to as being shorted.
Cover*: Purchase shares to cover those that were previously short sold. In the register the Short sale works and looks the same as a Buy transaction. This is the transaction that is used in order to repay the shares to the lender.
MiscInc: Miscellaneous income associated with a specific security.
MiscExp: Miscellaneous expense associated with a specific security.
Xfr: Transfer funds to or from this investment account. This action can also be used to record income or expenses transactions that are associated with the investment as a whole rather than with a specific security.
* Short and Cover transactions work in tandem while they show value in the account register they individually do not show any value outside of the register, only any difference in the value of the two transactions is shown outside of the register.
Moneydance allows for Quicken short names for these during import, there are a few variations. It works with XfrIn and XfrOut but changes it to the way Moneydance works as an example of one thing it does.
I do not have, nor have ever seen, a list of how it handles any other actions types. This would need some trial and error to work out or to get a peek at the code.
5 Posted by brec on 30 Mar, 2023 02:52 AM
dwg, this is all very helpful. One detail...
I've seen
XInandXOutalthough notXfrInnorXfrOut; also, the .qif I'm working with has someXIns andXOuts, though it would be easy to change them. I'm having trouble parsing the part of the sentence starting with "but changes it" -- to what does "it" refer?Would it be safe to assume that
XFRwould work to, in effect, say that, although in an investment account this is a bank-type transaction and so look at the amount sign to determine direction? I note that the .qif I'm working with usesTXFR.Is the source of that something you can provide?
I'm not expecting any peeks at the code, but I'll be doing some trial and error with toy accounts. I'm a lot more optimistic now about getting this migration done than I was earlier today. Thanks!
6 Posted by dwg on 30 Mar, 2023 03:19 AM
Yes when I typed XfrIn and XfrOut I was think elsewhere, it is XIn and XOut in Quicken.
XFR looks at the sign to determine direction in Moneydance and will work on imports - after all Moneydance can import its own QIF exports.
In Moneydance XIn should convert to XFR with a positive amount and the converse with XOut
The basic Moneydance valid action types came out of the drop down list in Moneydance, they have also been documented in the KB article at:
https://infinitekind.tenderapp.com/kb/investment-accounts/investment-account-registers
7 Posted by Stuart Beesley ... on 30 Mar, 2023 05:33 AM
(I withdraw my earlier response. I didn’t know you were talking about imports. I assumed you meant within MD itself)
8 Posted by brec on 30 Mar, 2023 08:40 AM
dwg, indeed in my toy account testing
Xfrand the appropriate amount sign works on import for both bank and investment accounts. I addedXfrto the list of Check# values.Strangely, though, for each of the
Xfrs I'm also getting a 0.00 amount transaction of the same date and category (counterparty account) as theXfr. The 0.00 transactions aren't harmful except for the clutter. I can delete them easily by sorting on Check# (which is blank for the 0.00 xactions) in the bank account and then selecting them as a group.9 Posted by dwg on 30 Mar, 2023 09:44 AM
As Moneydance follows accounting rules for every transaction you enter into a bank type register in the account or category specified in the category field there should be a transaction for the same date and amount in the register of what has been specified, in effect this is the other side of the transaction, so for a transfer between two accounts entering a transaction in one results in the other transaction being created in the other account.
Another way of putting it for every transaction there is always a source and a destination and thus you have an equivalent transaction in each. There is a register for every account and category in Moneydance.
If more than this is happening it raise questions. Things like if this is being loaded from a QIF file then there could be an issue in the file. You can possibly get interesting results if an account name duplicates a category name.
10 Posted by brec on 30 Mar, 2023 11:21 AM
dwg, yes, I have understood the double-entry rules but that doesn't explain the 0.0 transactions. In this toy setup there are two accounts, a bank and a brokerage. I loaded a toy .qif which has for each of these accounts one xfr in from the other account and one xfr out to the other account. Each account ended up with those two xfr transactions, as expected. But each also had a 0.0 transaction of the same date and category. Sure, each 0.0 was double-entered, but why were they created in the first place?
11 Posted by dwg on 30 Mar, 2023 12:00 PM
Can you provide us with a sample QIF file that has this result so that we can try to duplicate the problem?
You are describing something I have not seen before, and I have not seen reported.
12 Posted by brec on 30 Mar, 2023 12:07 PM
dwg, Sure, here it is.
13 Posted by dwg on 31 Mar, 2023 12:37 AM
Yes this file cause problems on import.
I think there are problems with the QIF file.
Just looking at the first few lines I see:
!Type:Cat
!Option:AutoSwitch
!Account
NChecking
D
TBank
^
I then expect to see a category list.
But I see Autoswitch, as far as I am aware autoswitch is only for account lists.
I then see !Account this should not be in a category list it is for use in Account lists
Then there is a N, this is the category name, but in your case I think it this is an account name.
D is for description and it blank that is fine.
Then there is a T in a category list this means Tax related, it is a toggle. if a T exists it is related if it does not exist it is not but it says Bank which to me implies this should be an account list.
I can see a problem in the investing section:
^
D2023/03/01
T-500
CX
TXIn
L[Checking]
^
All QIF instructions are a single character in line position 1, you have two "T" instructions, which is not allowed. Your second T should be a N with the action following it.
I haven't gone through the file line by line, but those are two problems that stood out.
14 Posted by brec on 31 Mar, 2023 01:53 AM
The problem in the investing section, T instead of N, is definitely a head-slapper. Fixing those two lines makes the result as expected -- two transactions per account, with the 0.00 transactions gone. Any issues in the prelude portion weren't harmful here, and I'm not concerned about them
There's also a CX line that got boosted to be the end of a T100,000.00.
Sorry to raise a false alarm caused by silly typos.
15 Posted by dwg on 31 Mar, 2023 02:25 AM
I would make the QIF file as clean as possible. When you get into large imports who knows what any inconsistency could result in.
Maddy closed this discussion on 04 Apr, 2023 09:37 AM.