Clearing old “online” bank / broker balance(s)

Method One ~ This is Michael Gorden’s QIF solution, submitted by Cal Learner

[UPDATED 2/3/2011 with fix for Broker as well]

Once your Microsoft Money program is offline (online services are no longer active), your downloaded bank balances will be updated just as your online updates did, but only if you go into Manage Online Settings  and remove the Online Update services.   Until that happens, the statement balance only reflects Online Updates, so if you would like to simply clear your statement balance to avoid some confusion, Cal Learner has published a cool little trick to clear the balance by creating a dummy statement to be imported and then immediately deleted.

Here is a 5-step way to do that:

1. Open a new Notepad file, and paste the following 6 (or 8 for broker file) lines into the file:
   For a Bank
   Save the file as
   “ClearBalance.QIF“.

   !Type:Bank
   D12/11/2010
   T-1.00
   PRemove Balance
   MUsed to remove the balance
   ^

   For a Broker
   Save the file as
   “ClearBalance_Broker.QIF“.

   !Type:Invst
   D12/11/2010
   T1.00
   NBuy
   YMMF
   I1.0000
   Q1.00
   ^

2. Change the date on the line that starts with ‘D’ to a date later than the reported last download in the register.  Today’s
   date should work.  The date format depends on your Windows regional settings.
3. Save the File->SaveAs  “ClearBalance.QIF” OR “ClearBalance_Broker.QIF” in the same folder as your *.mny file.
4. Open Money, and File->Import->Downloaded Statement.  Select “ClearBalance.QIF” OR “ClearBalance_Broker.QIF” and click Import.  Money will ask which account (or to make a new account).  Select the account you wish to clear the Downloaded Date from, and click Next, then OK.
5. Do not process the statement as you would a regular statement.
   Instead go to Account List and right-click where it says
   “Transactions to read:1”  for the account that you imported into and choose Delete Statement.

You can re-use the “ClearBalance.QIF” OR “ClearBalance_Broker.QIF” files on all the accounts you wish to clear.

The Bank / Broker balances should reappear accurately when statements are downloaded via OFX scripts, if you have turned off Online Update service for that Bank or Broker:

• Users should disable online access for all of their accounts in the existing Money data file (before switching to Sunset edition, as this may not actually work in that edition) and then SAVE and backup their data files [Account List->Manage Online Services. Click the account. Click Stop Using Online Services]. Data imports may not work as expected with accounts that are still configured for Online statements within Money and you may experience some weird Alert messages as well as ”Last Updated” column dates that will not update properly.

---

---

Method Two ~ This is Cheryl’s OFX file solution that is described in comment #698  below. Thank you Cheryl!

For anyone interested, this OFX file imports into Money 2007 Deluxe version 16.0.120.1303. It has also been verified to work with version 17 (Money Sunset Deluxe, as well as Home & Business).

Select quoted text below, copy and paste into a text editor and then save as “ClearBalanceRedux.OFX”

Make these changes:

1. Change BALAMT tag to be your desired ‘Bank Balance’ in MS Money. Be sure to change both instances of the tag.

2. Change ACCTID tag to your account number. Or don’t. If you don’t, then Money will ask you to choose an account from your list of accounts when it performs the import. Either way works.

3. Change ACCTTYPE tag to whatever type of account you’re aiming for. This example is for a checking account.

4. Change the DTASOF tag value if you want a different as-of date for the bank balance. The sample file is set to 2/16/2011. The format is YYYYMMDDHHMMSS. For example, to get 2/28/2011, you would change 20110216080000.000[0:GMT] to 20110228080000.000[0:GMT] (note both of those are timestamped as 8:00am)

Note: Don’t worry about the FID and FORG values. There has to be something in those tags or Money will kick out the file, but Money doesn’t actually interpret the values.

There are probably other tags defined in this file that aren’t required for a successful import (like ) but they don’t seem to be hurting anything so I left them alone.

5. Save the edited .txt file. Then change the name of the file, removing ‘.txt’ from it so that it’s an OFX file.

6. In Money, do File->Import->Downloaded Statement.

Note that this OFX will write create a $0 transaction in the account’s register. You can delete it 🙂 I experimented with removing the TRAN list but got errors from Money so I just left it in. A benefit of leaving it in is that I can use it to bring in transactions on purpose, and they’ll look like they came in from the bank as opposed to me creating them via Money register.

I hope this helps! I was sure happy to see my balances in line, plus I was able to set ‘Bank Balance’ values for banks with whom I’ve never been able to get transactions (like People’s Bank) so Yay! Now if only I could see the Bank Balance in the Account List screen, too. But that’s a struggle for a different day.

OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

<OFX>
	<SIGNONMSGSRSV1>
		<SONRS>
			<STATUS>
				<CODE>0
				<SEVERITY>INFO
			</STATUS>
			<DTSERVER>20110217192307.758
			<LANGUAGE>ENG
			<DTPROFUP>20050531070000.000
			<FI>
				<ORG>B1
				<FID>10898
			</FI>
			<INTU.BID>3000
			<INTU.USERID>
		</SONRS>
	</SIGNONMSGSRSV1>
	<BANKMSGSRSV1>
		<STMTTRNRS>
			<TRNUID>0
			<STATUS>
				<CODE>0
				<SEVERITY>INFO
			</STATUS>
			<STMTRS>
				<CURDEF>USD
				<BANKACCTFROM>
					<BANKID>000000000
					<ACCTID>enter_your_acct_number
					<ACCTTYPE>CHECKING
				</BANKACCTFROM>
				<BANKTRANLIST>
					<DTSTART>20110215080000.000
					<DTEND>20110216080000.000
					<STMTTRN>
						<TRNTYPE>DEP
						<DTPOSTED>20110215120000.000
						<TRNAMT>0
						<FITID>201102151
						<NAME>ADJUSTING TXN
					</STMTTRN>
				</BANKTRANLIST>
				<LEDGERBAL>
					<BALAMT>0
					<DTASOF>20110216080000.000[0:GMT]
				</LEDGERBAL>
				<AVAILBAL>
					<BALAMT>0
					<DTASOF>20110216080000.000[0:GMT]
				</AVAILBAL>
			</STMTRS>
		</STMTTRNRS>
	</BANKMSGSRSV1>
</OFX>

-ameridan