Scottrade Import and Reconciliation Issue

[elliots]

Mark,

Strange thing happened. Did an import from Scottrade and saw a questionable transaction in the pre-import listing. I let it go anyway. The account reconciled OK.

The transaction was an interest reinvestment that should have gone into the default cash account. Instead it went into an investment that had been sold down to 0 and was "hidden.*" That investment now has a purchase transaction for $3.18 @ $1 and a balance. The default cash account is short $3.18. The hidden investment has $3.18 it shouldn't The reconcile routine OKs the situation.

I believe you are using the CUSIP for matching transactions to investments. This investment pre-dated my use of FM and data was imported into FM when I started -- the CUSIP is blank. The CUSIP for the default cash account is also blank. I'm guessing that your import routine matched this transaction to the first blank CUSIP it finds and then brings it in. Since this hasn't happened with previous monthly interest credits I'm guessing the investment stack got resorted, or you made some change to the import and reconcile programs. I can't guess why the reconciliation routine is missing the 2 incorrect balances unless it is aggregating the 2 investments with blank CUSIP.

I did a custom report on the account in question and found many investments (I imported a lot of history) without CUSIP -- but the first blank in the list was the one that got the transaction. There are also several in the list without symbols (various old cash investments).

I can easily fix this transaction manually, but I'm concerned about the bigger issue -- I rely heavily on the reconciliation routine to tell me everything is OK or not. I think the process needs review and tightening.

Some suggestions. Use a combination of CUSIP and SYMBOL to match things up and require that ALL investment have a CUSIP and SYMBOL, even if the system has to force some "dummy" CUSIP during import or manual creation of an investment record. (The import could prompt for a missing CUSIP and offer the option of a created dummy that would follow some rules that indicated it was a dummy).

Your thoughts? I haven't corrected the data yet in case you want me to send you any files.

*This gets back to a previous discussion we had on hidden investments that get a new transaction and stay hidden. If I hadn't noticed the import was going into an investment I knew had been sold a long time ago, I wouldn't have know about it until I did a manual reconciliation of the account against a brokers statement. Maybe the import routines should flash up a warning message when a transaction is imported into a hidden investment?

[Mark]

Hi Elliot,

Yes, the institutional Scottrade interface uses CUSIP for matching. If you press the "Help" button in the transaction import dialog for Scottrade there is some description of this.

I think your analysis is right on, for why it went into that wrong investment. If you're using Scottrade FM will always fill in the CUSIP with the value in the Scottrade importing file. It sounds like however you initially imported (not Scottrade, or else they would all have CUSIPs), the CUSIP didn't get imported. I would suggest correcting these, and recording something for the CUSIP, even a dummy value. You can do it globally on a per-symbol basis under "Edit / Symbol List...". That would be faster than doing it on a per-investment basis in each account.

Reconciling with Scottrade institutional is also done via CUSIP, so all investments with the same CUSIP are combined into a single sum of shares for that unique CUSIP. Recording CUSIPs in everything will fix this.

[elliots]

Mark,

OK, I'll force in some dummy CUSIPS where needed. But I still think you should have an error trapping routine to prevent an investment from being added without a CUSIP, and some kind of alert/flag when a transaction goes into a hidden investment.

Elliot

[Mark]

Hi Elliot,

Okay, thanks for the feedback.