Investment Account Balancing

[Cal Learner]

A few times a year I balance my investment accounts by balancing the cash. So I am actually balancing the account in the cash/contributions account. The C column in the register can be blank, contain E which stands for Electronically balanced, or R for reconciled. I don't use the C column in the Investment Transactions register,

When I get an action, such as a spinoff, merger, or odd split, there are fractional shares produced, which then get sold automatically by the broker to leave whole shares. I enter the events as spinoffs, mergers or splits in Money, and Money can generate Sell transactions for the fractions. Fidelity, a few days later, puts a CIL (Cash in Lieu) transaction into the brokerage cash account. When those come in via PocketSense, I adjust the total for the fractional share sale, and I Void the CIL that was downloaded. Voiding, rather than deleting, ensures the CIL transaction will not come in again in a later download.

Occasionally I forget to void the CIL transaction. That causes a mismatch in the Cash Transactions register balance vs what the Fidelity History shows. This is not the only thing my balancing picks up, but also the occasional, but rare, missing or duplicated transaction. I don't download with PocketSense in the middle of the night, suspecting there may be a time when things are in a state of flux.

For my process, I set the Fidelity History to show a single account, and have the history stored in the same order I use in Money -- Sorted by date (increasing). I compare at various dates, and zero in on discrepancies. If somebody wants some more detail on balancing my Fidelity investment accounts, say so. I expect it is the same with other brokers. My method does not use Money's built-in balancing system.

Once I determine the account, at least up to a date, is balanced, I mark that cash account balanced in the Cash Transactions register. I change the View to show "Unreconciled transactions". I select the top entry, and hold down Ctrl+Shift+M. Auto-repeat will mark transactions reconciled at about 2 or 3 transactions per second. You can stop part way down, or keep going. When done, change the View to again show "All transactions".

If you have not done this for a very long time, you may want to add a single compensating transaction into the register dated for when you want to start comparing balances. I have an adjustments Category.

[Dick Watson]

FWIW, what I do:

I'm still a Luddite who has no downloaded transaction data in Money. About 100k transactions later (depending on how you count), mine are all still by hand. For Investment Accounts, I take one or two steps to assure I reconcile with the downloaded .PDF statements from Fidelity and Vanguard. (It's complicated why I have both of them. I used to have more FIs when we had 401(k)s and 401(k) SMAs. It's still two brokerage accounts with Investment Account associated Cash ("InvCash") accounts, two IRAs with InvCash accounts, and two Roth IRAs without InvCash accounts.) If I see a transaction online, I generally "C" mark it in Money. If I enter a transaction in Money because I saw it online, I try to be more conscientious about "C" marking it.

I balance the non-retirement InvCash accounts monthly using the Money InvCash More cash tasks...Balance an account native tool and the downloaded statement .PDF. This is no different than balancing any other Money cash-type account. Since one of the affected accounts is used as our checking account and the other is all but dormant most months, this goes quickly. For the two InvCash accounts associated with the IRAs, the "Balance an account..." choice brings up the dreaded "Update retirement account" "Wizard". It's terrible because it has bad side effects. I can't remember details--there may be a FAQ Q&A--because it's been quite literally decades since I tried it. I balance the retirement InvCash accounts by hand. The number of transactions here is minimal.

I balance share quantities in the Investment Accounts vs. share balances shown on the statement .PDFs quarterly. (Money doesn't provide a tool to do this for non-retirement accounts and the tool they provide for retirement accounts is, as noted, garbage.) I use either the Investment Account Summary view or Portfolio Manager Standard view, with an appropriate As Of date, to see the Money # shares balance and match that against statements. Those that don't match get further investigation. Once they match, I "R" mark all the Investment Transactions that are included in that balance. The only headache is that we have some positions that Fidelity only reports after the fact with backdated as-of dates. (I.e., the transactions aren't on this month's statements, but are in next month's statement share balance and are transacted at the as-of-date price.) These only cause a problem if I record Investment transactions, typically a Reinvest * transaction with a share qty, in Money, at the as-of date, before I balance to the statement ending that same as-of. In those cases, I typically either use the Portfolio Manager As-Of one day earlier share balance or, if other transactions with share qty impact on the as-of date are on the statement, I temporarily Void the "post-statement" transaction, balance shares, then un-void the problem transaction. Cases with share #s out of balance are, in descending order of likelihood, because a) I fat-fingered a share qty when entering a transaction, b) I missed entering a transaction, or c) I entered a transaction order at a guess price and never updated it when I got a confirm/online visibility to how it executed.

To keep investment position values "balanced", I update share prices manually for positions I hold. I do this as often as I see fit/have a need in for a precise account value Money, but typically only for the last market day of every quarter. Night before last, that was 15 quotes read from a browser window and typed into Money Update prices manually.

Your mix of accounts, transactions, investment types, and your trading practices all affect the choices that will work well for you in how you capture transaction information and make sure it stays consistent with the reality at the FIs. What I describe works well for me. Maybe just because I'm used to it.

On Sunday, January 2, 2022 at 5:35:31 AM UTC-10 Cal Learner wrote:

A few times a year I balance my investment accounts by balancing the cash. ...

[Ameridan (microsoftmoneyoffline.wordpress.com)]

Dick, thanks for describing your process.  Now that Schwab no longer provides ofx, I too am entering and balancing transactions by hand.  Regarding "C" mark and "R" mark (clearing and reconciling), I just discovered the keyboard shortcuts for same; Cntl + M and Cntl + Shift + M respectively, in case you weren't aware.

[Cal Learner]

Regarding Schwab, have you looked for a CSV to OFX converter that can produce a good OFX investment file? If the existing one don't work for that, maybe you could recruit Joe D.

Vanguard and Fidelity still provide OFX to PockeSense for investment accouts.

[Ameridan (microsoftmoneyoffline.wordpress.com)]

I've never tried a CSV to OFX converter, but since most of my transactions are either monthly recurring bill payments or recurring interest payments (all entered automatically), it's seems like it would be easier to just confirm the amounts, rather than exporting 7 different statements on a regular basis, and then putting each one through the conversion process.  I do miss using Pocketsense with Schwab though!!

[Ellen S]

a bit late to this thread, but FWIW, I also balance my invest accts manually via cash transactions, and manually enter invest transactions since I can't get OFX downloads.  Cash balancing isn't hard but entering investment transactions can be a huge time sink.   updating stock prices is the only Pocketsense download that works for me anymore.

Cal - how are you getting Fidelity OFX downloads?  they stopped for me long ago, whether directly or via Pocketsense.   In fact, none of my banks, credit card and investment firms provide OFX anymore (except CapitalOne, but I messed up the imports once and couldn't fix it).  (Sometimes I'll download/import a Quicken file if available, but I've only seen for banks not investments and usually not worth the trouble.  Also it seems the CSV to OFX converters only work with bank/cc accounts, not investment.)  A while back I checked my OFX settings but did not find new/different.   Can you or anyone advise if this is correct for Fidelity (assuming it still provides OFX)?   

<site>
    SiteName   : Fidelity Investments
    AcctType   : INVSTMT    #investment
    fiorg      : fidelity.com
    fid        : 7776
    url        : https://ofx.fidelity.com/ftgw/OFX/clients/download
    bankid     :
    brokerid   : fidelity.com
    appid      : QWIN
    appver     : 2400
    mininterval:
    timeOffset :
</site>

Here is the error message:  

[Fidelity] Error message:  ** An ERROR occurred sending POST request to ofx.fidelity.com, exception type : <class 'ssl.SSLError'>, Exception val : [Errorno 1] _ ssl:c:510: error:14077438:SSL routines:SSL23_GET_SERVER_HELLO:tlsvl alert internal error

[AMEX returns null statement received, Discover returns invalid OFX statement received, there are other responses for other banks/cards]  

Appreciate comments, thanks.

Ellen

[Cal Learner]

See if this helps:

url  :  https://ofx.fidelity.com:443/ftgw/OFX/clients/download

[Cal Learner]

Also, CitiCards work with PocketSense. Switching credit cards is not so hard, but you will need to change settings at those suppliers that auto-debit your card.

Citibank has flawed OFX web download, but it can be worked around.

If you do web download of QFX files, you will need to do something. The beta PocketSense has a way to do that something for you.

Fidelity works great, except avoid downloading between midnight ET and maybe 4 or 5 AM. I have a special Fidelity scrubber that avoids the problem of transactions having the wrong date. Some won't care if Friday's transactions are dated Saturday. I do.  I run a scheduled task that reads the Fidelity stuff about 6 AM ET Tuesday...Saturday. Vanguard works with PocketSense  for my limited access. 

[Ellen S]

got excited for a minute but afraid only getting error messages:  "Error processing the incoming request :"   Note: doesn't like ":443/" deletes it when searching.

I also tried ofx.fidelity.com.  "NOT FOUND    Sorry. The page you requested cannot be found on our site."

I use Edge, also tried Firefox, still no go.  

FWIW, customer service page mentions Quicken downloads, but I believe it is referring only to Intuit's automated retrieval.  says it will download only to young (3 yr old or less) software.   I suspect that means no manual qif downloads.  

[First Last]

> Here is the error message:  

[Fidelity] Error message:  ** An ERROR occurred sending POST request to ofx.fidelity.com, exception type : <class 'ssl.SSLError'>, Exception val : [Errorno 1] _ ssl:c:510: error:14077438:SSL routines:SSL23_GET_SERVER_HELLO:tlsvl alert internal error

Two possible causes that I know of

1. Old python which sends https requests that no longer deem safe. At the start of an https connection, the client and server go through an exchange of supported encryptions. If the client sends a no-longer-safe encryption, the server could terminate with the above error. To fix, see if you can update to the latest version of Python.

2. You are using a "proxy" for outgoing connection and the code to make outgoing connection has a bug: not setting the hostname field correctly. To fix, maybe update the PocketSense version?

The URL look OK, I use 

fi.url=https://ofx.fidelity.com:443/ftgw/OFX/clients/download

and that works for me. 

(For Fidelity NetBenefits, I use: fi.url=https://nbofx.fidelity.com/netbenefits/ofx/download)

Notes: I am not using PocketSense but a self-build tool (https://bitbucket.org/hleofxquotesteam/hleofxquotes/wiki/Download_OFX)

[Ellen S]

hleofx:   my Python is very old indeed, I have Python34 (2015) loaded but now see Python 3.10.5 just recently released.   Will try to update, if I can remember how.  Thx.

Can't speak to possible proxy issue.   

have not seen any pocketsense updates in a long while, thought they stopped, I now see two that I missed.   

FWIW, it was the web browser (Edge) kicking out "443", I see now that the url in my Sites.dat does not include "443" so I will put that in.  Maybe that will get Fidelity downloaded again.     

Not sure I have the skills to build my own tool but will look at the site you posted if the above steps fail to help.

thx

[Ellen S]

So:  Robert (Pocketsense) says use Python 2.7 NOT 3.  But 2.7 support ended 1/1/2020.   wondering which to download?

[Dick Watson]

I know next to nothing about Python, but I do know v3 was a radical departure from v2 in certain areas and lots of v2 Python is, supposedly, made unhappy by 3. If the Pocketsense guy says use 2.7, he probably knows whereof he speaks. For a tool like Python "support ended" for an older version means "it is what it will forever more be". It doesn't mean you shouldn't use it--except maybe as a target for new development.

[Cal Learner]

https://www.python.org/downloads/windows/ a 2.7.something. I think I am running their Python 2.7.12, so I would get something not earlier than that.