Transaction Download and Reconcile TD Ameritrade

[robd]

I have only had a TD Ameritrade (TDA) account since August, so I am easily able to download all transactions. However, when I do so, then try to reconcile, the default cash account is always way off. All other investments reconcile fine.

I am also able to download all transactions from my Fidelity account for the previous year and everything reconciles fine there including the cash account.

When I look on my TDA web page, it shows my balances. I can see that the statement amount shown in the FM reconcile window matches the TDA Cash Balance plus Money Market Balance. However the FM balance, from the downloaded transactions, is close to 3 times that amount. Has anyone else had this difficulty? Why won't the cash account come anywhere close to reconciling just from initial downloads with TDA?

Thanks,
Rob

[Mark]

Hi Rob,

Try using the New Portfolio Wizard (File / New Portfolio Wizard...) and choose the setup method of retrieving "Transactions and Positions". When you get to the step about a default cash account, choose "Cash", and leave on the option to record corresponding entry in default cash account. Right after you finish with the wizard, do a reconcile, and check your balances.

[robd]

I thought that is what I had done before but this time it worked, so I must have done something different in my previous attempts. Thanks.

I notice the first entry in Cash is "Adjustment to obtain retrieved balance". That amount matches the amount it was off before. Since, my account with TDA is so new that I can download all historical transactions, why would this type of adjustment be necessary? Is there something more concrete to what this amount represents rather than a mysterious adjustment?

Thanks,
Rob

[Mark]

Hi Rob,

This is typically your cash balance on that starting date. When FM retrieves positions and transactions, it gets all available transactions, and the current positions. If all the transactions don't add up to the current balance, then an adjustment entry is made at the earliest date. In the case of Cash this is usually your starting cash balance in the account. You can look at all your recorded transactions in the Data Register (Edit / All Data...) and see what you retrieved, and that may shed light on why the adjustment was necessary.

[robd]

Mark,

I have gone through all of the transactions from the time when my starting balance was zero. All transactions were successfully download to FM. So the total that FM has for my Cash account is correct. The statement amount coming back from TDAmeritrade when I attempt to reconcile does not equal the same amount.

On TDA's balance page they list four amounts Cash Balance, Margin Balance, Short Balance, and Money Market Balance. The amount that comes back as the statement amount for reconcile is only the Cash Balance plus the Money Market Balance. However, all four of those values must be added together to equal the total amount in the FM cash account. So it appears that either TDA is not giving you the total cash amount or your request is not asking for the right value.

Just as a comparison, when I do the exact same things with my Fidelity account, everything matches perfectly without any adjustment. So there should be no need for an adjustment with TDA either.

Since I'm still new to FM, I definitely may not be using it correctly but with everything that I have laid out, doesn't it look like the TDA reconcile is not working correctly?

Thanks,
Rob

[Mark]

Hi Rob,

When reconciling cash, Fund Manager adds up your Available Cash minus Margin Balance as reported by your broker to compare against your default cash account. To debug this, you can turn on logging (Options / General Preferences... / Other / Create Log Files When Importing Transactions). Take a look at the xmldump.txt log file. You will see something like this:

- INVBAL
- AVAILCASH (1500.00)
- MARGINBALANCE (-100.00)
- SHORTBALANCE (0)

Your default cash account should be the sum of AVAILCASH minus MARGINBALANCE. In the above example, your default cash account should have a balance of 1400.00 to reconcile. Besides default cash, everything else is listed in the INVPOSLIST section.

[robd]

That option is turned on but I can't find a file with that name. Where should it be located?

Rob

[Mark]

Hi Rob,

It will be in your user's "AppData \ Roaming \ Fund Manager" folder. The "AppData" folder is hidden, so you have to display hidden files/folders.

[robd]

Now I found it. It looks like TDA's Balances & Positions page does not match corresponding values in the OFX download. That's something I will need to contact them and ask about.

The "adjustment to obtain retrieved balance" amount in the FM reconcile exactly matches the Short Balance amount on the Balances & Positions page and it also matches the value of the short stock in the OFX file.

So, as you say, the adjustment is based on the AVAILCASH - MARGINBALANCE. If I create the portfolio later in the day, after the market closes, the adjustment makes it so everything matches up, including the cash account, for reconcile. However doesn't the AVAILCASH - MARGINBALANCE value change throughout the day when the market is open? If so, that would mean reconcile would balance fine on the cash account when the portfolio was created. But the next day, reconcile will no longer match on the cash account. Is that correct? My Fidelity portfoio always seems to reconcile. Is that simply because I am not short any stocks or options right now at Fidelity?

Thanks,
Rob

[Mark]

Hi Rob,

Interesting, it sounds like the funds from your short positions are not being reflected in the available cash? Do you see anywhere in the log file that your short position cash is reflected? I didn't quite follow this:

If I create the portfolio later in the day, after the market closes, the adjustment makes it so everything matches up, including the cash account, for reconcile.

I wouldn't think the AVAILCASH - MARGINBALANCE would change throughout the day, unless you had some transactions that affected cash. Maybe during the day your short cash is in some other account, not considered available, until the end of the day?

[robd]

Even when I have had no trades during consecutive days, after the market closes each day, I can attempt to reconcile and the statement amount that comes from TD Ameritrade is different every day. Therefore I can never truly reconcile after the first time. Without accounting for the short balance in the calculation, I don't think the cash account will be able to reconcile. Has anyone else had difficulty reconciling with TD Ameritrade? I do not have this difficulty with Fidelity. But then I don't have any short positions at Fidelity either.

Rob

[Mark]

Hi Rob,

Is this because some money is moving from the "Short balance" to "Available Cash" amount? What exactly is changing in these days where you have no transactions. If Fund Manager used the combination of AVAILCASH - MARGINBALANCE + SHORTBALANCE for your default cash account balance, would that solve this problem?