Clearing old “online” bank balance(s)

[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 come up with 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
    between the ===delimiter lines=== below into the file.
    For a Bank
    Save the file as
    ClearBalance.QIF“.
    ========begin ClearBalance.QIF==================
    !Type:Bank
    D12/11/2010
    T-1.00
    PRemove Balance
    MUsed to remove the balance
    ^
    ========end ClearBalance.QIF===================

    For a Broker

    Save the file as
    ClearBalance_Broker.QIF“.
    ========begin ClearBalance_Broker.QIF==================
    !Type:Invst
    D12/11/2010
    T1.00
    NBuy
    YMMF
    I1.0000
    Q1.00
    ^
    ========end ClearBalance_Broker.QIF===================
  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.

P.S.  This is Cheryl’s OFX file solution that is described in comment #698  below. Thank you Cheryl!

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

35 Comments »

  1. Cheryl said

    Hm, this worked for me before (Nov2010), but today I’m getting an error from Money when I try to Delete Statement. It’s nice & vague…it says “The operation cannot be performed”. Anybody else seen this? I have all online services turned off and I’m following instructions to get to Delete Statement. I get a dialog box that wants to know if I really want to Delete Statement. I say ‘ok’, then I get ‘the operation cannot be performed’.

    Has anyone else seen this? I’m using Money 2007 Deluxe 16.0.120.1303 if that matters.

  2. ameridan said

    Cal’s file only works for Bank Statements; the fix is different for Brokers as I recall. If Cal pipes in (or I find it somewhere), I’ll add that to the Blog page but I suspect that is why it isn’t working for you.

    [EDIT] I found it and posted it in the Blog page as “Fix for a Broker”

  3. Ken said

    I’m telling you, the easiest way to get rid of those now meaningless Last Updated and Bank Balances is to shrink the column width to nothing. It takes care of all accounts with one action.

  4. ameridan said

    Ken,

    Those columns aren’t meaningless to all of us. Those columns reflect up-to-date balances and dates for me with Schwab, Discover, etc. It all depends on whether the OFX file is a “Statement” file or not.

  5. Cheryl said

    I was trying to use this to clear a Chase checking account, then I tried it with a BofA checking account. Since they aren’t Brokers, seems like it should work. Last year, it did work for me, although I think that was a Credit Card, so maybe that’s the difference.

    I could shrink the column, but I do use that column to make sure my active accounts are getting updated. It’s a detail that the bank balance & my local balance aren’t the same, not really important since my budgets all work with local balances, but still, thought I’d ask.

  6. Ken said

    Maybe I’m missing something. Once I switched over to Pocketsense I found that for ALL accounts that downloaded via Pocketsense I no longer got correct Bank Balances or Last Updated info. The Adjusted Balance, which reflects the posted transactions in Money is correct but the others stopped updating. Are you still getting correct info for the 2 columns I got rid of? The only accounts where those columns are still accurate are those where I have to go to the institution’s website and download the file from there. For me that’s only a couple minor accounts, but I may be the exception.

  7. Ken said

    The only account I think we have in common is Discover and it’s showing a last updated date of 1/2/11 and the balance from that date. That was the last time I did an online update.

  8. Cheryl said

    Very cool, that will be useful to clear a Broker. But I’m just trying to clear a checking account, so I thought ‘Bank’ would work. Hm.

  9. ameridan said

    I found Cal’s other file and added it to this Blog page.

    Ken, if you clear the “online statement” using the instructions above, then your info should be updated. Look in the top left corner of your Discover Account register. Maybe you are referring to another summary screen, but my “Account List” screen is also updated.

    Cheryl, did you change the date? Perhaps this page might also help you – https://microsoftmoneyoffline.wordpress.com/reg-file-to-empty-the-statement-import-que/

  10. Ken said

    I just tried the ClearBalance_Bank for my Discover card account. Followed the instructions exactly. It’s still showing Last Updated 1/2/11 and there’s no change to the Bank Balance. Tried it on an AMEX account, too and both the Last Updated and Bank Balance are unchanged. Same with Chase Checking account. I can tell that it’s seeing the $1 transaction and then removing it but that’s it. Any ideas?

    As for the ClearBalance for brokers, got an error message when I used it on a Fidelity account: An unaccepted QIF header was encountered. Transactions under the header have been skipped.

    May just go back to narrowing the columns 🙂

  11. Ken said

    And it didn’t change the upper left corner of my Discover card account register, either.

  12. Cheryl said

    Yep, I changed the date to be 02/03/2011, so that it would be after the last txn loaded to that account. The statement imports fine, meaning it changes the ‘adjusted balance’ and I see 1 transaction to review in the Account List, and yet I just can’t seem to Delete Statement. ClearBalance.QIF looks like this:

    !Type:Bank
    D02/03/2011
    T-1.00
    PRemove Balance
    MUsed to remove the balance
    ^

    I’ll try the RegEdit to get rid of the statement. Thanks for that link.

  13. ameridan said

    Cheryl & Ken (and others may find this useful as well)

    Are you using Sunset edition? Cal wasn’t having luck either with Sunset…

    http://social.microsoft.com/Forums/en-US/money/thread/3dc79ebc-e2be-4734-9fab-1d87581b9358/#8fd376de-7e88-442b-8968-25dafffff360

    On the AccountList page of my Sunset instance, the “Last Updated” column displays a date from when I copied the Money file from my non-sunset Money Plus Deluxe file. Similarly, it has “Bank Balance” values at the top of the Cash/Contributions account of Investment accounts. I would have hoped these values would get updated during the import of the OFX files, but they haven’t.

    The ClearBalance.QIF method works for bank accounts, but I have not been able to get this to get rid of the unwanted values in the Cash/Contributions account of Investment accounts. If somebody has had success in this, please let me know.

    until I advised him that it looks like you have to do all this prior to upgrading to the Sunset edition

    Did you turn off Online updates for the financial institution(s) holding your Investment accounts in Money Plus prior to awitching to the Sunset edition? I think that is the key as those balances no longer show up in any of my Investment accounts.

    to which he responded…

    That was the solution, ameridan. Thanks I had just copied the file as is from the regular Money Plus Deluxe on the desktop computer to the laptop computer with Money Plus Deluxe Sunset. What I should have done is to make a copy of the *.mny file on the desktop, and turned off online services on that copy for each FI, and then moved that copy to the laptop computer.

    To fix it I could have just done that now, and ignore the file I had been working with on Money Plus Deluxe Sunset. Instead I copied the file from the laptop back to the desktop, disable online access in that file, and copy it back.

    What I also tried that did not work is to turn off online access using Money Plus Deluxe Sunset. The control was there, and it appeared to work. However it did not have the desired effect of clearing out the fields.

    Thanks again.

    That is another advantage of using Money Plus on one computer and Money Plus (Sunset) on another computer – you can go back and forth with the MNY file as they are both version 17 programs.

  14. Cheryl said

    I have been tempted to move to Sunset, but to-date, I am still in regular old 2007 Deluxe. Good thought, though.

  15. Cheryl said

    I did the registry edit, but still can’t Delete Statement. I verified that I do have online services turned off (they’ve been off for a while now)

    Ah, well, in the scheme of things, it’s not a big deal. Thanks for thinking about this, though.

  16. Ken said

    I’m still on Money Plus but all Online updates are disabled (after 1/31 they were automatically grayed out). My guess is that your clearing out fix needed to be applied before 1/31. After that date I think MS may have downloaded changes equal to Sunset that disabled the ability to reset those fields. I’m pretty sure that’s the case as I only was hibernating my computer from 1/30 to 2/2 and was still getting quote updates as before. But as soon as I rebooted on 2/2 that functionality went away. I think unless someone comes up with a new hack I’m stuck with the dates and bank balances from the last online update. If so I’ll just collapse the columns. It’d be nice to get last update dates and current bank balances but, as Cheryl said, in the scheme of things, it’s not a big deal.

  17. ameridan said

    Very interesting tidbit regarding the patch / reboot. Seems like the version level would have changed – is it still 17.0.xxx.1415 Ken? That may explain why some are still getting the quotes after 1/31. Well, I tried! We’re all learning and as Ken said, the rules may have changed this month.

  18. Ken said

    Being one who loves a challenge I went back to see how to accomplish clearing the Last Updated and Bank Balance columns. Turns out there’s a simple and effective way (and no, it doesn’t require reducing column width :). Here are the steps:
    Go to the Account List page
    Select Manage Online Services in the left column
    On the Manage Online Services Page, for each of the financial institutions for which you received direct downloads before 1/31 you’ll see Update Online Services to the right in the To Do column.
    Click on Update Online Services, then Accept, then OK (these steps may take several seconds)
    Each time you do this the financial institution will be moved to the bottom grouping for institutions that don’t provide online updates.
    Go back to the Account List page and the Last Updated column will have today’s date for all accounts associated with that institution and the Bank Balance will be correct.

    I’ll double check tomorrow when I run PocketSense that the columns are updating properly and post an update to this.

  19. Cheryl said

    Thanks, Ken. The thing is, I wasn’t receiving direct downloads for this account prior to 1/31. I stopped having access to online services via Money in Nov2010 (I launch Money without them), hence my move to PocketSense.

    The trouble is that I closed the account but Chase removed my online access before I was able to get an OFX file with the last transaction.

    When I go to Manage Online Services, all of my accounts have no service. The To Do column says “set up online services” or “check for new online services” or “complete setup for online services” but of course whenever I click those, I’m told that online services are turned off. Sigh.

    I think you and I are using different Money versions. I have Money 2007 Deluxe 16.0 and I think you have Money Plus 17.0 so maybe that’s why you were still getting online access until 1/31.

    I’ve been pondering this all evening. It’s so weird that I can import a statement and it applies itself to my Adjusted Balance & tells me I have a transaction to review. So the statement is accepted and applied. But when I try to delete that statement, it’s just “The operation cannot be performed”. Talk about a dead-end no-op error message.

  20. Ken said

    I can confirm that the Last Updated and Bank Balance numbers are now correct and updating when I use PocketSense. So it looks like I’m set. When I ran PocketSense this morning I did have to match my accounts to the downloads but I’m hoping that’s a one time issue to properly sync the downloads to the correct accounts.

    Cheryl, yes, I am on Money 17 and had online access until 1/31. Guess my method won’t help you. Sorry. But you can always shrink the column widths 🙂

  21. Ken said

    Dan, in answer to your question I’m still showing 17.0.120.1415 as my release number. I think the changes that were downloaded on 1/31 just “updated” the online services provided by all the institutions rather than changing the software.

  22. ameridan said

    Thanks for the updates Ken! Now you know why I have underlined portions of the statement:

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

    Your method was just another means of turning off the individual online update services 🙂 Don’t know why Cheryl isn’t having any success…

  23. Ken said

    I had seen your comment in bold and had thought it meant removing the password and unchecking the box to update that institution. Other than that I couldn’t see any other way to turn off Online Update service. Out of curiosity, how were you turning updates off?

  24. ameridan said

    Just added this to the Blog…

    • Users should disable online access for all of their accounts in the existing Money data file 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.
  25. Cheryl said

    OK, I admit that this has been bugging me, even though I said it wouldn’t. But I have a solution, finally. All I did was hack a freshly-downloaded QIF OFX file that worked for an active account. The file I used only had one transaction in it, so it was easy to manipulate. I changed the account number, fid, forg, and the txn amt, then I made sure the balance was ‘0’. Then file->import->Downloaded Statement. Money changed the bank balance to zero and brought a zero-dollar txn to my register. I was able to delete that txn (instead of accepting it) without affecting the bank balance.

    Since I seem to be the only person having this issue, I’m not sure if I should post the dummy QIF OFX file, but if anybody does want me to, I’m happy to share.

  26. Ken said

    Congratulations, Cheryl! I knew if you were anything like me you wouldn’t be able to let it go 🙂 Since online updates went away 1/31 I’ve been using PocketSense and Hung Le’s Java app to get quotes without a hitch. There really isn’t any difference worth mentioning between the old online method and the new one. What a relief! And for the one account that discontinued providing OFX downloads from their site, importing QIF files (which is supported) works like a charm. Hope all is working as well for you.

  27. cindy said

    Cheryl, could you please post that OFX I can not get Chase or Discover to clear. TIA

    • Cheryl said

      Sure. Which version of Money are you using, Cindy?

  28. Cheryl said

    For anyone interested, this QIF OFX file imports into Money 2007 Deluxe version 16.0.120.1303. I don’t know if it works with any ’17’ version.

    http://www.mzskunk.com/ClearBalanceRedux.OFX.txt

    [EDIT by ameridan – I also pasted Cheryl’s file as a P.S. to the blog (above) as another alternative (select All, copy and paste into a text editor and then save as “ClearBalanceRedux.OFX”). Also changed references of QIF to 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 a .QIF OFX file.

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

    Note that this QIF 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.

  29. ameridan said

    Cheryl,

    Money 2007 should process downloaded statements the same as Money Plus (& Sunset).

    I can clarify clarified your comment for you, but this is an OFX file, not a QIF although in the end it imports the same though 🙂 Thanks for submitting what looks to be a more robust alternative to Cal’s QIF file trick.

  30. Cheryl said

    Right, yes, this is an OFX file. Sorry about the misnomer. I forgot that I changed the extension a while back to see what would happen (nothing) and didn’t change it back. Thanks for the clarifications.If you want to change the references to “QIF” in my comment to “OFX”, I’ll do the same to the online “.txt” file.

  31. Cheryl said

    Cool. There’s a file called “ClearBalanceRedux.OFX.txt” now if you want to change the link.

    http://www.mzskunk.com/ClearBalanceRedux.OFX.txt

  32. ameridan said

    I think the changes are all made now. Thanks again Cheryl.

  33. ameridan said

    Cheryl,

    Regarding your comment “Now if only I could see the Bank Balance in the Account List screen, too. But that’s a struggle for a different day.”, the LEDGERBAL data does show in the Last Updated and Bank Balance columns of the Account List page for me (for bank accounts).

    Dan

  34. Cheryl said

    I’ve hacked together an OFX file that zeroes out credit card accounts, if anyone’s interested. A nice bonus is that is does write the Bank Balance and Last Updated on the Account List screen.

    It also writes a dummy transaction, for $0, that can be deleted after import.

    It’s at http://www.mzskunk.com/ActivityClearCCBalance.OFX.txt

    I’ve learned that Money will remember the ACCTID->Money Account mapping, even if it’s nonsense, once you choose the account from the drop-down list upon initial import. This means that if you want to clear out more than one account, you have to modify the value of the ACCTID tag on the 2nd import, even just by one character, or else Money will keep importing to the first account that you told it to.

    I’m not sure if that made sense!

RSS feed for comments on this post

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: