a sale corrupts cash balance?

[Bruce]

I have multiple portfolios. Every month all of them are manually reconciled. I don't do automatic downloads from brokers; only historical and current priced data.

One portfolio, we sold / closed 4 positions in September and bought 1. Somehow entering those sales has created a $2,805.66 shortfall in FM's cash account. This error has propagated back through time to when the portfolio was created; i.e. FM no longer reconciles to past statements even though it did at that time.

I've checked my entries for the sales to make sure my data entry reflects the correct date and values (and they do). There is no part of any of the transactions that match $2805.66 (or half it).

What stumps me is how the error propagated backwards through time.

Thoughts? Is there any way I could have entered the data such that FM would ... I don't know ... update historical transactions based on a current transaction? Something else?

This is only happening in this portfolio. I have several others, and no issue with those.
Hope you can help

[data8504]

Hi Bruce,

Let's take this in two steps. The mystery and the fix.

For the mystery:
1) Can you please check the date FM reports the portfolio in question was last reconciled? You can do this by attempting to manually reconcile, select the portfolio in the dropdown, then come back with the "Last reconciled" date.
2) Go to: Options > General Preferences > Data, is the checkbox "Warn when modifying reconciled data" selected?

FM will remove reconcile dates if you modify historical data. Thus, you can check if you inadvertently inserted transactions in the past by confirming this date. (this of course assumes that by "manually reconcile" above, you literally mean that you use FM's feature here, not just a check of the portfolio editor.)

For the fix:
1) How far back do you have to go for this subportfolio's core position to reconcile w/ statements? Literally all the way to the beginning? (this is what you said in your problem statement, but I want to confirm.)
2) Are all mis-reconciles always the exact same amount? (again, this is my inference from your post above, but please confirm.)
3) If (2) is "yes," then an errant transaction must exist prior to any mis-reconcile. Can you confirm there are no transactions in the data register prior to your first "expected" transaction?
4) Can you confirm that you're reconciling core position *share* count, not dollar amount? (this is the behavior of FM's reconcile tool, but if you're just looking at the value in a report, there could be some weird price thing going on too...)

Let me know, and I can help you further debug.

-Matt

[Bruce]

1. Was 7/31/17
2. Yes (and I know that part works)

questions under "fix"
1. Yes, the opening balances are now wrong. 4/17/15.
2. All same amount? Yes and No. (sorry). They are the same amount for months (like to Dec 16) and then it changes and are the same amount for a few more months back, then changes ....
3. This seems like a multi-part question. First, 4/17/15 is the earliest transaction where everything that was inherited appeared as "bought" in FM. That left a negative cash balance, which was offset by a "journal" entry in cash of the same amount only positive, giving me "zero" cash balance. Then the correct amount to match inherited cash was entered as a purchase.
3.B: I started auditing the cash register against the broker's cash statements. More on this later.
4. In the portfolio window, right-click the folder object and select "reconcile". "Manually Reconcile" is selected, hit next, set the date to the statment's end-date, and reconcile the numbers in that window against the statement.

[Bruce]

I mentioned your question 3 seemed like two part, and that I started auditing the cash register backwards (time) against the broker's statements. I found odd behavior and missing data.

In mid July over several days I opened positions in ~half-dozen funds. These are funds I have never owned before, so of course I had to create the asset in FM. As I audited the cash register I found two of the eight purchases did not appear. The cash register is the only place they didn't appear. The funds, the correct number of shares and the correct prices show up everywhere else (in the right window .. portfolio details window, reports, etc). I can double-click on them and their registry window opens and everything looks correct there.

Both have paid cash dividends since July. I have recorded the dividends in the fund's register and they have shown up as a purchase in the cash register correctly.

As part of investigating, I thought maybe something's wrong just for share-purchases. To test that, in one fund I made an entry to buy 1 share about 10 days after the original opening purchase (7/17). Everything recorded correctly ... my share count incremented by 1, and the cash registry showed a "redemption" and a memo "bought 1 <fund name>".

That proved the "buy" function worked right. At this point I have maybe 8 hours in auditing and I just want to get the register corrected and move on. So I think I'll edit my 1 share purchase transaction to match the amount I purchased on the opening day. Worked great. I now own double what I should, with one purchase on 7/17, and one purchase on 7/27, every where but the cash register. Only the 7/27 purchase shows in the cash registry.

Fine so far. So I delete the "bad" purchase on 7/17. Now I own the correct number of shares everywhere (I verified portfolio window, reports, reconcile window) and I have the correct numbers in the cash register, except everything is on the wrong date. So I go back to the fund's register and edit that purchase date to 7/17. I'm back to square one; the transaction that was on 7/27 that should have just moved to 7/17, disappeared from the cash register. But it still shows in the portfolio window and still shows in reports. Just my cash balance is off.

My next thought is to delete the two offending assets including their dat file and try to start over.

In FM I have 13 real portfolios with and this one portfolio is the only one that has given me grief. Unfortunately it is the second largest in terms of number of assets.

[Mark]

Hi Bruce,

Sorry to hear about the troubles. It is hard for me to say exactly what went wrong, but here are a few tips:

- Make sure you have a default cash account assigned. If you're missing some "cash" side entries it may have been because at the time the transactions were recorded, you didn't have a default cash account assigned. If you are missing some cash entries, you can simply re-record them. You can add a buy/sell of cash.

- If you have some error that goes back in time, then there must be something wrong with transactions on/before that date where the error starts. The share balance on any given date is simply the sum of all transactions up to and including that date. So, if you have an error on 1/1/2000, it will propagate all the way forward from that date.

- You might want to watch our Reconciliation tutorial.

[Bruce]

I'm not sure what I was to get from the reconcilliation tutorial ... it all looks like what I do / expect except I use paper statements and FM doesn't connect to the brokers.

So I agree with what you said ... if there's an error that propogates back in time there must be a bad entry back there somewhere. I know where it is; it's the first entry in the cash register when I created the account. But it was right a year+ ago when I created it as it reconciled for months. Then it had an error and a I fixed it. Then it reconciled find for 7 months and now it's wrong again. I don't know what's effecting the entry in the original purchase of cash.

Regarding having a default cash account assigned: Is it possible that 22 of the 24 assets in that account recognize the cash account but somehow I created two new assets that refuse to recognize it on a particular day? i repeated the "purchase one share" test. (reminder, correct purchase date is 7/17).

I entered a purchase of 1 share on 7/18. It is recorded correctly in all views including cash. I edited the purchase to 7/16. It is recorded correctly in all views, including cash. I increased the amounts of the 7/16 purchase to equal the 7/17 purchase. The 7/16 purchase records correctly every where including Cash.

To be clear, and this point all views in FM except Cash show double what I should own. Cash only shows the dummy purchase that is now on 7/16. Seems obvious: delete the "bad" 7/17 purchase and let the dummy purchase be correct. Since I did that before and the result was the good entry disappeared from the cash register, let me try just setting the bad 7/17 entry to 0.0001 for value (since I can't enter zero).

Now in the assets register I have a dummy purchase on 7/16 with the correct numbers and a purchase of essentially zero on 7/17. In the cash register I have on line on 7/16 with the right numbers.

Now I'm going to move the bad "zero" entry to 7/18 and the correct "dummy" entry to 7/17. All in the asset register. The cash register looks good: I have a line on 7/17 for redemption, and the memo field correctly says bought 500 <fund name>.

Now if I were a Finance guy, i'd stop here and just live with a "zero" entry in the register. A Finance guy always blames the software anyway. But I'm a retired system architect with lots of testing background. I'm dying to know if I can get the same bad behavior to repeat.

Last time I created a correct dummy entry and as long as I left the bad entry alone, everything was fine. But when I deleted the bad entry, the good entry also disappeared JUST from the cash register.

So go to the asset register where I have three lines: The "good" dummy entry that mirrors the real transaction, the "bad" real entry that shows up everywhere but the cash register, and one dividend (that correctly shows up in the cash register). I highlight the bad entry only. I click the delete button at the bottom of the register window, and .... Everything is fine. The failure I got this morning did not repeat. I did two things different before deleting the bad entry: First, I essentially zeroed (value = 0.00002) the bad entry, and second, I moved it to a different date. Then i deleted it.

If I do figure this out, I WILL let you know.

[Mark]

Hi Bruce,

Is it possible that 22 of the 24 assets in that account recognize the cash account but somehow I created two new assets that refuse to recognize it on a particular day?

Yes. Your default cash account settings are used at the time any transaction is recorded. It is possible that somehow you didn't have your default cash account assigned for a particular time when you created the 2 investments. It may be set correctly now, but that only affects transactions that you record while these settings are in place. It sounds like things are set and working correctly now. If you're missing some cash transactions, then you can just record them. There is no way to definitively know what the default cash account settings were previously when you recorded prior transactions. You can only infer from seeing if there are recorded/missing cash transactions.

Since I did that before and the result was the good entry disappeared from the cash register, let me try just setting the bad 7/17 entry to 0.0001 for value (since I can't enter zero).

Something doesn't sound right here. If you select a particular transaction and delete it, only that selected transaction should be deleted. Is this some problem you can reproduce? Were you deleting a transaction? Maybe you were deleting a price instead? You can enter a zero value for a transaction, so there may be some confusion here on what you're actually editing. Look in the Data Register in the upper/right corner, and see what data type you're viewing/editing. For fixing share balance errors, you want to be looking at transactions, not prices.

[Bruce]

I don't remember exactly when I started using FM. Maybe 2 years ago. This account was the first I created. The the two assets that are causing me heartburn are new to me as of 7/17/17.

"Something doesn't sound right here." Yea! That's the kind of thing I'm looking for. When I enter a transaction on an existing asset, I double-click that asset and the Data Register opens with Data Type set to All Transactions. I hit Alt+N for "new" and D for a "distribution" or B for "buy/sell", and I fill out that pop-up window. I have some assets that Yahoo doesn't give quotes for and I have to hand-enter prices periodically, so I do know (and would recognize) the window that goes with Data Type: Prices.

Now I've reproduced the "buy" of zero and you're right; it can be zero value, but not zero shares.

I'm going back to plowing through the cash register looking for more funny business.

FYI: I exported the cash's price file to excel, sorted on the "close" column to look for errant entries. All are "1".

[Mark]

If you can reproduce the situation you described where a different transaction is deleted than the one you selected, please let me know.

[Bruce]

To review & summarize, the original problem was Opening Transaction A existed in the asset's register but not the Cash's register, discovered since the cash balance wouldn't reconcile. Before I discovered the whole A:B thing below, I pulled old paper statements and tried to reconcile Cash against them, and they didn't reconcile back to 4/2015, when the account was created. Then I noticed that the purchase of two new funds in 7/2017 didn't show in the Cash register but showed everywhere else. So I'm thinking somehow the process of automatically creating the corresponding debit in the Cash register got corrupted for two of three opening buy transaction on 7/17. Note there were several other opening buy transactions on several days following, and they all worked correctly.

I was thinking the process that created the debit in the cash registry had been corrupted and I just needed to delete the old transactions and re-enter them.

So I created a near-duplicate Transaction B in the asset's register. Transaction A and B now exist in the asset's register, but only Transaction B shows in the Cash's register. I delete Transaction A from the asset's register; Transaction B remains in the asset's register, except it disappears in the Cash's register. Note I label opening transactions with a memo like "open purchase". I put a "dummy transaction" in the memo of Transaction B (and something else in C, later), so I'm pretty sure I didn't delete B by mistake.

I repeat the experiment by creating a Transaction C but this time changing the date of Transaction B, (C shows in the Cash register and B still does not). Then I changed the Value of B to zero, (C still shows in the Cash register and B still does not, but the Cash register balances for the first time( note 1)). Now I delete B and a miracle: C remains in both the asset register and cash register.

note 1: Remember, these transactions are all 7/17/17. Somehow Cash now reconciles all the way back to 4/2015. I can't imagine how Transaction A would get linked invisibly (because I looked) to cash pre-4/2015 and B did not (because it showed up in Cash on 7/17/17), until I deleted A and then B somehow got linked pre-4/15. When I create C, then I manually changed the date of B to 7/18/17 before trying to delete it, and that somehow broke the bad date link. This of course is all wild conjecture on my part attempting to explain what I spent 16 hours trying to audit and figure out.

There were two different assets I had to do that "Transaction A transform to Transaction C" to to get something to show up in the cash register correctly. Both entered on 7/17/17. There was one opening purchase on 7/17/17 that worked fine (for a total of three).

Something really goofy here, but whatever it is, it now reconciles. I've done a new "save as". If it happens again (which I'd prefer not), I'll let you know before I try to fix it. Maybe we can get on the phone and try to test it real-time. I'm happy to say it's keyboard error or that I set the account up wrong back whenever I piloted FM. But right now it is still a mystery.