Money '07: Partial database reset?

I'm using Money '07 Deluxe.

Due to my own damn fault, my Money database is a mess: my checking,
savings, credit card and money-market accounts have not been properly
kept up in years, nor reconciled. So now they don't match reality. I
want to fix this before I get into financial trouble (again).

I can retrieve from my bank's online service OFX statements going back
to Jan 1, 2007 but no earlier. So what I'd like to do is basically
"zero out" those accounts' balances and transactions, and use that data
as my new starting point while keeping the accounts' configurations
intact. So obviously, I'd rather not delete them and re-add them if I
can avoid that.

Also, and this may become irrelevant with the above, I downloaded and
imported OFX based e-statements from the bank, and on two of the four
accounts I'm told by Money that I have statements to read, but when I
go to them I see from the notes at the to of the left-side margin that
there is a number of items to review, but they are not visible in the
account register despite setting it to show all transactions.

--
Jeffrey Kaplan www.gordol.org
The from userid is killfiled Send personal mail to gordol

"You are a creature, who's received pain and given pain and taken too
much pleasure in it's application." (Amb. Delenn, B5 "Comes The
Inquisitor")

Cal
Sat Feb 23 15:00:11 CST 2008

In microsoft.public.money, Jeffrey Kaplan wrote:

>I'm using Money '07 Deluxe.
>
>Due to my own damn fault, my Money database is a mess: my checking,
>savings, credit card and money-market accounts have not been properly
>kept up in years, nor reconciled. So now they don't match reality. I
>want to fix this before I get into financial trouble (again).
>
>I can retrieve from my bank's online service OFX statements going back
>to Jan 1, 2007 but no earlier. So what I'd like to do is basically
>"zero out" those accounts' balances and transactions, and use that data
>as my new starting point while keeping the accounts' configurations
>intact. So obviously, I'd rather not delete them and re-add them if I
>can avoid that.

You may be that person that will find that Archive does what he
wants. Archive will keep a copy of the existing file as the archive.
The continuing file will delete the non-investment transactions that
precede the archive date, and it will adjust the balances.

Now you will probably need to re-adjust the starting balances to
reality.

Alternatively, delete the accounts that you will re-download. Set up
new ones and download. Adjust the starting balances for the
accounts. Transfers to non-deleted accounts will be something you
will have to attend to.

Copying and pasting the bank statement into Excel may let you make a
running balance. Oddly, when I paste an statement from a web page
statement I am looking at, I need to paste it "special" as text.


>
>Also, and this may become irrelevant with the above, I downloaded and
>imported OFX based e-statements from the bank, and on two of the four
>accounts I'm told by Money that I have statements to read, but when I
>go to them I see from the notes at the to of the left-side margin that
>there is a number of items to review, but they are not visible in the
>account register despite setting it to show all transactions.

In Internet Explorer delete your browser temporary files with
Tools->InternetOptions->DeleteFiles or
Tools->InternetOptions->Delete->DeleteFiles.

Then in Money do File->RepairMoneyFile->QuickFileRepair again.

Then go to Tools->Settings->OnlineServiceSetting and
select ShowStandardizedPayeeNamesForWhichANameIsNotFound. This
is a global (inter-file) setting.

Download again.

Jeffrey
Sat Feb 23 22:21:54 CST 2008

Previously on microsoft.public.money, Cal Learner-- MVP said:

> You may be that person that will find that Archive does what he
> wants. Archive will keep a copy of the existing file as the archive.
> The continuing file will delete the non-investment transactions that
> precede the archive date, and it will adjust the balances.
>
> Now you will probably need to re-adjust the starting balances to
> reality.

I started up Money to start following your suggestions, and Money
indicated that it was updating my accounts. Apparently, it never
finished the first time and now was. The balances still don't match,
but at least now they're in the same neighborhood. One's off by about
two grand, but that's a vast improvement from earlier in the day when I
started trying to fix this.

And, of course, something like 95% of the transactions have incorrect
Payee info ("Withdraw" transaction for $35.85? That's gotta be a
gasoline purchase...). And archiving won't "fix" this, since they're
all within the period I'm going to keep. (Gotta remember, it's my own
damn fault this happened...)

> Alternatively, delete the accounts that you will re-download. Set up
> new ones and download. Adjust the starting balances for the
> accounts. Transfers to non-deleted accounts will be something you
> will have to attend to.

If I do the archive thing, I presume I can adjust the starting balance
via the Account Details.

> Copying and pasting the bank statement into Excel may let you make a
> running balance. Oddly, when I paste an statement from a web page
> statement I am looking at, I need to paste it "special" as text.

Why would I want to use Excel, when I have Money for this?

I'm not copying a statement from the web, I'm downloading a series of
*.ofx files (one per account) that since Money was open at the time,
automatically prompted to import.

> >Also, and this may become irrelevant with the above, I downloaded and
> >imported OFX based e-statements from the bank, and on two of the four
> >accounts I'm told by Money that I have statements to read, but when I

Never mind, see above.

--
Jeffrey Kaplan www.gordol.org
The from userid is killfiled Send personal mail to gordol

"The Superior Person's Book Of Words", by Peter Bowler: CARDIALGIA:
Heartburn, i.e., mild indigestion. To the uninitiated, however,
cardialgia sounds like a serious disorder of the heart; hence suitable
for excuses, sympathy-seeking ploys, etc.

Cal
Sun Feb 24 00:19:35 CST 2008

In microsoft.public.money, Jeffrey Kaplan wrote:

>
>And, of course, something like 95% of the transactions have incorrect
>Payee info ("Withdraw" transaction for $35.85? That's gotta be a
>gasoline purchase...). And archiving won't "fix" this, since they're
>all within the period I'm going to keep. (Gotta remember, it's my own
>damn fault this happened...)

Check out the advanced FindAndReplace to change Payees etc for
transactions that have enough commonality to search on. You can then
change category or payee to what you want.

Also consider the PayeeRulesManager. Here are some Payee-handling
suggestions:

Go to
Accounts&Bills->Categories&Payees->Payees->PreferredPayeeNames or
Banking->AccountTools->Categories&Payees->Payees->PreferredPayeeNames
which shows how downloaded names are translated into Payees.
Record the Payee Name Variation(s) shown for the wrong payee.
Delete the entries that correspond to the wrong payee.

Then click on GoToPayeeRulesManager. Excluded Payee Names
identifies words and phrases that should be ignored from the payee
field (e.g., check, deposit). Add each word or phrase that was
listed as a Payee Name Variation that you recorded.

While you are at it, it is usually a good idea to select
Tools->Options->OnlineServices->ReplacePayeeNames....
Tools->Settings->OnlineServiceSettings->ReplacePayeeNames....

You may want to set
Tools->Settings->OnlineServices->AddADescriptionToTheMemoField






>
>> Alternatively, delete the accounts that you will re-download. Set up
>> new ones and download. Adjust the starting balances for the
>> accounts. Transfers to non-deleted accounts will be something you
>> will have to attend to.
>
>If I do the archive thing, I presume I can adjust the starting balance
>via the Account Details.

Yes. Alternatively set that to zero and make a single compensating
transaction with an early date to represent the opening balance.
That way it is easy to get to.

Also consider increasing the number of backups that Money keeps.
File->Restore is your undo.

Jeffrey
Mon Feb 25 11:25:24 CST 2008

Previously on microsoft.public.money, Cal Learner-- MVP said:

> >And, of course, something like 95% of the transactions have incorrect
> >Payee info ("Withdraw" transaction for $35.85? That's gotta be a
> >gasoline purchase...). And archiving won't "fix" this, since they're
> >all within the period I'm going to keep. (Gotta remember, it's my own
> >damn fault this happened...)
>
> Check out the advanced FindAndReplace to change Payees etc for
> transactions that have enough commonality to search on. You can then
> change category or payee to what you want.

I'll try to keep that in mind if it happens again - By the time I saw
your message, I had already manually edited them all.

In case it matters, the Payee field on them was about 48% each
"Withdraw" or empty, with the remaining 2% split between "Transfer of
Finds" and something along the lines of "Rev Trans". In all cases
except for the "Transfer of Funds" items, the Memo field had the full
merchant info. The "Transfer of Funds" items only had PayPal info, not
who I was paying through PayPal.

> Go to
> Accounts&Bills->Categories&Payees->Payees->PreferredPayeeNames or
> Banking->AccountTools->Categories&Payees->Payees->PreferredPayeeNames

The second path exists in this version of Money.

> which shows how downloaded names are translated into Payees.
> Record the Payee Name Variation(s) shown for the wrong payee.
> Delete the entries that correspond to the wrong payee.

That list looks like it's been auto-populated by prior manual edits in
the register. Unfortunately, unless there's a setting to read the info
from the Memo field into the Payee field, reconciling such downloaded
transactions in the future will remain a manual job.

> >If I do the archive thing, I presume I can adjust the starting balance
> >via the Account Details.
>
> Yes. Alternatively set that to zero and make a single compensating
> transaction with an early date to represent the opening balance.
> That way it is easy to get to.
>
> Also consider increasing the number of backups that Money keeps.
> File->Restore is your undo.

I have it set to keep 3 backups. It ignores that and keeps all
backups, requiring me to periodically manually delete old backups.

--
Jeffrey Kaplan www.gordol.org
The from userid is killfiled Send personal mail to gordol

"If I'm the president, we're going to have emergency-room care, we're
going to have gag orders." - George W. Bush, Oct 18, 2000

Jeffrey
Fri Feb 29 16:03:43 CST 2008

Previously on microsoft.public.money, Cal Learner-- MVP said:

> >If I do the archive thing, I presume I can adjust the starting balance
> >via the Account Details.
>
> Yes. Alternatively set that to zero and make a single compensating
> transaction with an early date to represent the opening balance.
> That way it is easy to get to.

I did the archive thing. On one of the accounts, it did not remove any
entries of a Paycheck transaction, I had to remove them manually. The
balances were off on most accounts, so I entered an adjustment
transaction for those.

When I downloaded and imported the ofx files, all of the matching
transactions were "accepted" and are marked with an "E" in the
register. Is there any way to mark them as "reeconciled" en masse, or
will I have to do that manually?

--
Jeffrey Kaplan www.gordol.org
The from userid is killfiled Send personal mail to gordol

DISCLAIMER: Handle with extreme care: This product contains minute
electrically charged particles moving at velocities in excess of five
hundred million miles per hour.

Cal
Fri Feb 29 22:50:00 CST 2008

In microsoft.public.money, Jeffrey Kaplan wrote:

>
>When I downloaded and imported the ofx files, all of the matching
>transactions were "accepted" and are marked with an "E" in the
>register. Is there any way to mark them as "reeconciled" en masse, or
>will I have to do that manually?

If you wish to declare an account balanced, you can mark all
entries balanced / Reconciled. Select to show only unreconciled
transactions. Sort by date, and go to the oldest transaction.
Hold down Cntl+Shift+M and let auto-repeat work thru the
transactions until none are left showing. Then you could balance
making the starting and ending balance match the known balance.
Choose the balance date to be the day after your last transaction.

Jeffrey
Sat Mar 01 10:19:53 CST 2008

Previously on microsoft.public.money, Cal Learner-- MVP said:

> If you wish to declare an account balanced, you can mark all
> entries balanced / Reconciled. Select to show only unreconciled
> transactions. Sort by date, and go to the oldest transaction.
> Hold down Cntl+Shift+M and let auto-repeat work thru the
> transactions until none are left showing. Then you could balance

That is still manually, but it is easier/faster than going through the
full Reconcile procedure. Thank you.

> making the starting and ending balance match the known balance.

My ending balances are now in sync, marking old stuff as reconciled is
the last step (I hope) to bringing everything up to date.

> Choose the balance date to be the day after your last transaction.

Not that it matters, but I'm only going to do this mass-mark through
the end of December for now, wait until I get my next statement then
resume to the statement's period and then move forward normally.

--
Jeffrey Kaplan www.gordol.org
The from userid is killfiled Send personal mail to gordol

Peter's Top 100 Things I'd Do If I Ever Became An Evil Overlord, #135.
My doomsday machine will have a highly-advanced technological device
called a capacitor in case someone inconveniently pulls the plug at the
last second. (If I have access to REALLY advanced technology, I will
include a back-up device known as a battery.)