Get Online Statements into MS Money using Python Scripting
As discussed in the introduction, I headed down the path of modifying some Python scripts to suit my own needs shortly after reading about it on the TheFinanceBuff website. The code has since been changed pretty extensively, and can be downloaded below (free), along with some quick instructions on how to install and use it. I did not intend to "take up the torch" and blaze a path on this, but felt that this version of the code may be useful to others. Since first posting, I've received many comments and suggestions, and some nice code enhancements too. The package has reached a very stable state, so you should feel pretty comfortable that you aren't going to hurt anything by trying. Comments and suggestions are welcome.
You will see that installation does require you to do a little work yourself, but don't be overwhelmed by what "looks" to be complicated. It's actually very straight-forward and easy to install/configure. It requires editing a file with a text editor (e.g., Notepad) to describe your financial institutions in a sites.dat file, and then entering your account access information using the Setup routine. The instructions appear to be "long", only because I've tried to complete. If you go to the effort to get one site and account working correctly, you will see just how easy it is to add more. If you have questions, feel free to ask.
a. Install ActivePython
- Download the ActivePython installer to your computer and install it. The PocketSense package is developed and tested using Python versions 2.7.x and earlier. I have not tested on the newer 3.x platform.
b. Install the PocketSense Package (Updated: 17-Feb-2015 18:10 EST)
- Download the ZIP file.
- Create a directory on your computer to extract the contents into.
- Unzip the files to the directory.
Upgrading to a new version? If you are currently using the PocketSense scripts, it is easy to upgrade to the latest version. Simply extract all of the files from the ZIP file to the install directory and overwrite the old files. None of the user-specific files (e.g., account and site settings) will be altered when upgrading. Additionally, take a look in the sites.template file when upgrading, to see what new user options have been added. These options will not automatically be added to your sites.dat file, since they really are optional. You can add any switch/option from sites.template to your sites.dat file, if you want to over-ride the default value.
c. Install Microsoft Money
- You probably already have Money installed, but if you have issues or need to perform a fresh installation, you can download a free version. Microsoft released a Sunset Edition of Money Plus Deluxe that can be installed on new machines without the need to perform online activation.
Setup/Configuration Summary (See below for details of each step.)
- If this is a new installation, create a fresh sites.dat file (for your settings) by running Setup.
- Edit the sites.dat file to include your site information (required), and optionally, your stock, and mutual fund ticker symbols.
- Run Setup.py to configure your account information.
- Prepare Money to receive quotes by creating a "dummy" investment account (optional).
- Run Getdata.py to download your account statements, and (optionally) stock/fund quotes.
2.1 Create Sites.dat
- Run (double click) Setup.py first.
- Select 0. Exit to exit. This creates a fresh sites.dat file, where you will define your Site information. Do not run Setup again until you have edited sites.dat to include your actual institutions.
2.2 Edit Sites.dat
Add your site information
file is the only file that you need to manually edit. There's no reason to edit the other files unless you intend to modify the code itself. You can use any text editor, but I highly recommend Notepad++ (free), since it won't insert hidden codes, or try to save your file with the wrong extension.
Transaction Period to Download
- Open the sites.dat file using a text editor, such as Windows Notepad. If you don't have a sites.dat file, run Setup.py (double click) and then exit the Setup routine. This creates a fresh, local sites.dat file.
- Go to the OFX Home directory and look up your institutions. If you have problems finding the info there, or if you want to be a little more aggressive with your search, then have a look at the MoneyDance data dictionary.
- Create an entry for each institution in the sites.dat file, following the format of the examples provided. Note that each entry contains an AcctType field that indicates whether it is a Credit Card (CCSTMT), an Investment (INVSTMT), or a Bank (BASTMT) account. Be sure to enter the correct code for the institution.
- Note that the name of the site entry (SiteName) must be unique, but that you can create multiple entries for a single institution, so long as each has a unique name. For example, if you have both a bank account and a credit card with identical site information, create a separate <site> entry for each.
- You do not need to create a site entry for each account at an institution; only one site entry is needed for each type of account. For example, you may have three different investment accounts at Vanguard, but you only need a single Site entry for Vanguard investments.
- Each site is wrapped within an XML-like structure <site>your site data</site>, which is required. Also, each data field must occupy a single line.
- There are numerous parameters that can be configured in sites.dat, and each is described in the sites.template file. Most parameters can be left to their default value, but after you have your accounts configured and working, you may want to play with a few of these to optimize performance to your taste.
There is a setting in sites.dat named defaultInterval, which defaults to a period of 7 days. This interval is the period back in time (in days) that transactions will be collected. To minimize the need to reconcile duplicate entries that may occur during the transition from the built-in downloads to the scripts, it's a good idea to keep a short interval and run the script every few days. After you have been doing this for a couple of weeks, increase the interval to 14 days. After you're well past the overlap with previous transactions, increase the interval to a longer period (30, 60 or 90 days).
Note: As of Jan-2013, there is a new option available in sites.dat named
(Default=No). If set to "Yes", then you will be prompted for the download interval each time Getdata is run.
2.3 Run Setup.py
The Setup.py utility is mostly self explanatory, but here are a few notes.
- Adding Accounts: Add each account that you wish to download statements for. When adding accounts, you will be asked if you want to test the new entry. It is HIGHLY recommended that you answer Yes. Doing so will test the new account and then send the data to Money. If the OFX data has never been imported before, Money will ask you to associate the file with an account. Select the account in Money when prompted. If you wait and run all accounts at the same time, you will likely get confused, since Money will not tell you which institution or file it is currently importing.
If you accidentally select the wrong account when first marrying an OFX statement to your Money file, read
to fix it.
- Account numbers: When entering account numbers, be sure to include leading zeros for accounts that require them (e.g., Account# 0001234 usually must be entered as 0001234). See the FAQ if you have the (unusual) need to assign the same site/bank account number to multiple Money accounts.
- Encryption: Account information will include user-names and passwords, so you will probably want to encrypt the information stored in your configuration file. Select Encrypt file from the main setup menu and follow the prompts. If you do not do this, the account information will not be encrypted. Encryption can be enabled or disabled at any time, and does not effect adding, editing, or removing accounts.
User settings are stored to a file named ofx_config.cfg.
By default, the Getdata.py program will not download stock and fund tickers listed in the setup.dat file. You can enable this functionality in Setup, following the instructions above.
- Choose a password with a length of at least three (3) characters. The password is CaSe sensitive and accepts any symbol, number or character (except for blank spaces).
- Once encrypted, you will be asked for your password anytime you attempt to use or edit your account information.
- You can remove encryption by selecting Remove Password Encryption (this options is visible when your settings are encrypted).
2.4 Prepare Money to Receive Your Statements
- After you have configured your accounts using the Setup.py script (steps 1 and 2 above), you are ready to download transactions.
- Create a backup of your Money file before first using the GetData.py script. It's actually a very good idea to use a copy of your account when first testing and configuring the scripts, or you can also create a new, empty Money file for testing.
- For current Money users (not Sunset Edition): Before using your actual, live Money file, it's a good idea to first delete existing Online Setup settings. Doing so will make all existing transactions look as though they were manually entered. Money will then try to match downloaded transactions to the "manually entered" transactions. This isn't required, but I think it's a good idea, and it's what I did myself. Ignore this step if this is a new install of Money Sunset, since there's nothing to delete.
OPTIONAL: Create an Account in Money to Receive Stock Quotes
Note: Stocks and funds that are in a Money account, for which you download statements, will update automatically when you import that account statement. You do not need to update those separately.
- OFX bank and brokerage statement imports will automatically update stock and mutual fund prices in their associated brokerage and bank portfolios.
- If you want to download and track quotes for other stocks and mutual funds from Yahoo!, you need to create a special "dummy" account to receive the quotes. Personally, I created an empty investment account and named it "Tickers". You can call it anything, and it won't have a dollar figure associated with it. It's simply a place for Money to stick the stock/fund symbols from the statement. When asked for an account "type", make it an Investment Account, but it won't hold any investments.
- Edit the stock/fund ticker symbols in the sites.dat file. Match ticker symbols to those used by the Yahoo! Finance web site, since quotes are received from their server.
- Enable "stock/fund" quotes by running Setup.py and selecting option 6. Enable Stock/Fund Quotes.
- You will be asked if you want to test the feature. Choose Yes and, assuming all goes well, you'll be asked if you want to send the results to Money. Choose Yes again, and when prompted by Money, select your dummy account to receive the stock/fund quotes. You won't be asked again, unless you delete the account.
3. Download Statements and Quotes
: If you didn't test an account when configuring in Step 2.3.1, you will be prompted to match with an account during import. It is highly recommended that accounts be tested when first configured using Setup.py, rather than trying to match them at this point, to avoid confusion.
Congratulations! Once configured, Getdata.py is the only program you will run on a routine basis.
- Run Money. It's best to have Money open before running Getdata, otherwise you will be prompted to open it.
- Run Getdata.py by double-clicking on it. You'll be asked to confirm the operation.
- If you encrypted your account information when running Setup, you will be asked to enter your password.
- At the prompt Download transactions? (Y/N/I=Interactive) [Y], chose y, n, or i. Choosing option i will run Getdata in "interactive" mode, which prompts you at various steps. Choosing "yes" will automatically download all statements, and send to Money.
- Each account will be processed, and automatically loaded into Money.
- After you're satisfied that all of your accounts are downloading and importing correctly, you may want to enable the CombineOFX option in sites.dat. Doing so will send all data to Money as one large statement, resulting in a single "accept" button in Money. Do not enable this option until you are sure that individual downloads are working.