Import data? M'07

Since Yodlee seems to have died on me (again), I'd like to manually
download and import data from my bank's web site to Microsoft Money
(2007) to electronically match/clear my transactions. However, the
last time I tried doing that I wound up with duplicates with mismatched
names instead. Is there some trick to it?

I can download data from the bank in user-specified date ranges, and
any of the following file formats: OFX, QFX, QBO or CSV.

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

"If I am ever the Hero top 100 list: #70. Being captured by the Evil
Overlord is one way to learn his secret plans, but there are
innumerable other ways that are better, and they will be tried first.

Cal
Tue Jul 08 18:04:50 CDT 2008

In microsoft.public.money, Jeffrey Kaplan wrote:

>Since Yodlee seems to have died on me (again), I'd like to manually
>download and import data from my bank's web site to Microsoft Money
>(2007) to electronically match/clear my transactions. However, the
>last time I tried doing that I wound up with duplicates with mismatched
>names instead. Is there some trick to it?
>
>I can download data from the bank in user-specified date ranges, and
>any of the following file formats: OFX, QFX, QBO or CSV.

Best bet: OFX.

Now with *many* web interfaces, you can limit how far back the
transactions go. So in that case, select a date to give a small
overlap.

Manually Void duplicates from the OFX or (delete or void the
earlier-downloaded transactions). When Money thereafter downloads
the same transaction in OFX, it will know it has seen that
transaction before, even if you Void the transaction.

So there will be *initial* extra work, dealing with duplicates will
be automatic in the future.

Jeffrey
Tue Jul 08 20:45:35 CDT 2008

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

> Best bet: OFX.
>
> Now with *many* web interfaces, you can limit how far back the
> transactions go. So in that case, select a date to give a small
> overlap.
>
> Manually Void duplicates from the OFX or (delete or void the
> earlier-downloaded transactions). When Money thereafter downloads
> the same transaction in OFX, it will know it has seen that
> transaction before, even if you Void the transaction.
>
> So there will be *initial* extra work, dealing with duplicates will
> be automatic in the future.

Sounds like I'll actually have to do that every time with each set of
new downloaded data. After all, I'm not going to be downloading the
same date range each time. My first one will be to the then current
date, my next one will be from that date to the next-then-current date,
etc.

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

"If I am ever the Hero top 100 list: #70. Being captured by the Evil
Overlord is one way to learn his secret plans, but there are
innumerable other ways that are better, and they will be tried first.

Cal
Tue Jul 08 21:29:08 CDT 2008

In microsoft.public.money, Jeffrey Kaplan wrote:

>Previously on microsoft.public.money, Cal Learner-- MVP said:
>
>> Best bet: OFX.
>>
>> Now with *many* web interfaces, you can limit how far back the
>> transactions go. So in that case, select a date to give a small
>> overlap.
>>
>> Manually Void duplicates from the OFX or (delete or void the
>> earlier-downloaded transactions). When Money thereafter downloads
>> the same transaction in OFX, it will know it has seen that
>> transaction before, even if you Void the transaction.
>>
>> So there will be *initial* extra work, dealing with duplicates will
>> be automatic in the future.
>
>Sounds like I'll actually have to do that every time with each set of
>new downloaded data. After all, I'm not going to be downloading the
>same date range each time. My first one will be to the then current
>date, my next one will be from that date to the next-then-current date,
>etc.

No. Just don't download things older than the first time, and you
should not have to deal with dups after the first time. For example,
you could use 6/6/08 as a starting date from now on, and never have
a duplicate. Money will note the OFX ones it has already ,even if
Voided, and ignore them.

Jeffrey
Wed Jul 09 09:26:06 CDT 2008

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

> >Sounds like I'll actually have to do that every time with each set of
> >new downloaded data. After all, I'm not going to be downloading the
> >same date range each time. My first one will be to the then current
> >date, my next one will be from that date to the next-then-current date,
> >etc.
>
> No. Just don't download things older than the first time, and you
> should not have to deal with dups after the first time. For example,
> you could use 6/6/08 as a starting date from now on, and never have
> a duplicate. Money will note the OFX ones it has already ,even if
> Voided, and ignore them.

One of us is not understanding the other, and it might be me.

The last time I tried to do this manually, I had a mess of dupes to
clean up as MS Money failed to match the transactions from the
downloaded ofx file to the transactions I had previously entered into
Money. This happened with the first file which had one date range and
the second time with the next date range (I had downloaded multiple
consecutive date ranges in one month blocks for three months that had
no downloaded data). I had dealt with the dupes by deleting the
downloaded mismatched transactions, which is what you are suggesting I
do.

And I had to do the same thing with the next set of data, and the next.

Since you're saying it's possible to do what I want, I must've been
doing something wrong, but I appear to have done what you are telling
me to do, unless I'm misunderstanding something.

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

"If I am ever the Hero top 100 list: #70. Being captured by the Evil
Overlord is one way to learn his secret plans, but there are
innumerable other ways that are better, and they will be tried first.

Cal
Wed Jul 09 10:01:11 CDT 2008

In microsoft.public.money, Jeffrey Kaplan wrote:

>Previously on microsoft.public.money, Cal Learner-- MVP said:
>
>> >Sounds like I'll actually have to do that every time with each set of
>> >new downloaded data. After all, I'm not going to be downloading the
>> >same date range each time. My first one will be to the then current
>> >date, my next one will be from that date to the next-then-current date,
>> >etc.
>>
>> No. Just don't download things older than the first time, and you
>> should not have to deal with dups after the first time. For example,
>> you could use 6/6/08 as a starting date from now on, and never have
>> a duplicate. Money will note the OFX ones it has already ,even if
>> Voided, and ignore them.
>
>One of us is not understanding the other, and it might be me.
>
>The last time I tried to do this manually, I had a mess of dupes to
>clean up as MS Money failed to match the transactions from the
>downloaded ofx file to the transactions I had previously entered into
>Money.

Hand-entered and not marked as reconciled or electronically cleared
(C column was empty)? Then matching should have worked better for
you than it did.


> This happened with the first file which had one date range and
>the second time with the next date range (I had downloaded multiple
>consecutive date ranges in one month blocks for three months that had
>no downloaded data).

Downloading different date ranges? I don't know how to think about
that. I think it does not affect the discussion.

> I had dealt with the dupes by deleting the
>downloaded mismatched transactions, which is what you are suggesting I
>do.

Not really. I was suggesting that you either
1. Void (*not* Delete) the new duplicate from the OFX download
or
2. Either Delete or Void the pre-existing transaction that was
duplicated by the OFX download.

#1 is easier because you can group the new transactions by order of
entry. Alternatively you can select "No" when processing the
individual transactions if the choice is offered and if you find
that easier; they are entered as Voided. #2 is harder, but may look
better due to not necessarily having Voided transactions hanging
around.

For the OFX download to recognize that it already has the particular
transaction, you need to have it in the register, voided or not.

>
>And I had to do the same thing with the next set of data, and the next.
>
>Since you're saying it's possible to do what I want, I must've been
>doing something wrong, but I appear to have done what you are telling
>me to do, unless I'm misunderstanding something.

Also make sure that you have stopped using online services for the
account that you are switching to using OFX web access with.

You may want to use two files in parallel for a bit to see which is
working better for you.

Jeffrey
Wed Jul 09 12:05:52 CDT 2008

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

> >The last time I tried to do this manually, I had a mess of dupes to
> >clean up as MS Money failed to match the transactions from the
> >downloaded ofx file to the transactions I had previously entered into
> >Money.
>
> Hand-entered and not marked as reconciled or electronically cleared
> (C column was empty)?

Yes.

> Then matching should have worked better for
> you than it did.

That's what I figure.

> > This happened with the first file which had one date range and
> >the second time with the next date range (I had downloaded multiple
> >consecutive date ranges in one month blocks for three months that had
> >no downloaded data).
>
> Downloading different date ranges? I don't know how to think about
> that. I think it does not affect the discussion.

When manually downloading the ofx from the bank's web site, I specify
the starting date and the ending date of the file's data. This way,
the user is not always downloading a file that is increasingly old and
already-handled data from prior downloads.

> > I had dealt with the dupes by deleting the
> >downloaded mismatched transactions, which is what you are suggesting I
> >do.
>
> Not really. I was suggesting that you either
> 1. Void (*not* Delete) the new duplicate from the OFX download
> or
> 2. Either Delete or Void the pre-existing transaction that was
> duplicated by the OFX download.

Ah, I see now.

> #1 is easier because you can group the new transactions by order of
> entry. Alternatively you can select "No" when processing the
> individual transactions if the choice is offered and if you find
> that easier; they are entered as Voided. #2 is harder, but may look
> better due to not necessarily having Voided transactions hanging
> around.
>
> For the OFX download to recognize that it already has the particular
> transaction, you need to have it in the register, voided or not.

Now we're back to old vs new. I fully see that if I download the
+same+ data again, that old data will be handled properly. But that is
not my concern. My concern is +new+ data being downloaded and matched
(or failing to match) to new transactions.

To give a specific example, I pay my utilities online. The gas company
is "Northern Utilities", so that's what I enter into the register. When
downloading transactions, it shows up as "WITHDRAW NORTHERN UTILITIES"
followed by a process number, all in the "pay to" field. When Yodlee
was working, MS Money would properly match these two as the same. When
importing an ofx file manually, MS Money fails to match them.

> Also make sure that you have stopped using online services for the
> account that you are switching to using OFX web access with.

That makes sense. If the online access through money hasn't started
working again by the end of the week, I will.

I found a "contact us" link at yodlee.com, but I don't hold much hope
for a fix, as they seem to not know what an end-user is and MS, of
course, disclaims any responsibility (and thus refuses to help) because
"it's a third party service".

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

Tips for the Evil Henchman: 26. The recommended method for checking
to see if the Hero is still alive is to shoot him in the head.

Cal
Wed Jul 09 12:25:42 CDT 2008

In microsoft.public.money, Jeffrey Kaplan wrote:

>
>When manually downloading the ofx from the bank's web site, I specify
>the starting date and the ending date of the file's data. This way,
>the user is not always downloading a file that is increasingly old and
>already-handled data from prior downloads.

In the future, I suggest always using the same start date and using
the latest-available end date. Or at least don't be afraid to
overlap the start date with what you have downloaded with OFX
already. That is to say, don't worry about downloading the same OFX
transaction as you did before.

Before accepting, look over the transactions that did not match to
hand-entered transactions. Click Change to select the match if Money
does not do it for you. The ShowStandardizedPayeeNames... setting
discussed below may make your matching much better in the future.

>
>> > I had dealt with the dupes by deleting the
>> >downloaded mismatched transactions, which is what you are suggesting I
>> >do.
>>
>> Not really. I was suggesting that you either
>> 1. Void (*not* Delete) the new duplicate from the OFX download
>> or
>> 2. Either Delete or Void the pre-existing transaction that was
>> duplicated by the OFX download.
>
>Ah, I see now.
>
>> #1 is easier because you can group the new transactions by order of
>> entry. Alternatively you can select "No" when processing the
>> individual transactions if the choice is offered and if you find
>> that easier; they are entered as Voided. #2 is harder, but may look
>> better due to not necessarily having Voided transactions hanging
>> around.
>>
>> For the OFX download to recognize that it already has the particular
>> transaction, you need to have it in the register, voided or not.
>
>Now we're back to old vs new. I fully see that if I download the
>+same+ data again, that old data will be handled properly. But that is
>not my concern. My concern is +new+ data being downloaded and matched
>(or failing to match) to new transactions.
>
>To give a specific example, I pay my utilities online. The gas company
>is "Northern Utilities", so that's what I enter into the register. When
>downloading transactions, it shows up as "WITHDRAW NORTHERN UTILITIES"
>followed by a process number, all in the "pay to" field. When Yodlee
>was working, MS Money would properly match these two as the same. When
>importing an ofx file manually, MS Money fails to match them.

Tools->Settings->OnlineServiceSettings->ShowStandardizedPayeeNames...
will cause you to match an existing payee that differ only by a
numbers or punctuation. Click MoreDetails to the right of the
setting for more information. I think this should handle the
"process number" problem.

After that setting is made, change the Payee manually before
accepting the transaction. Money should catch on next time.

I am going to put a little Payee write-up at the bottom of this
posting.

>
>> Also make sure that you have stopped using online services for the
>> account that you are switching to using OFX web access with.
>
>That makes sense. If the online access through money hasn't started
>working again by the end of the week, I will.
>
>I found a "contact us" link at yodlee.com, but I don't hold much hope
>for a fix, as they seem to not know what an end-user is and MS, of
>course, disclaims any responsibility (and thus refuses to help) because
>"it's a third party service".

Again, I would keep two different files for a bit before deciding
which method to go forward with.

---------------------PAYEES-----------------------------

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->Settings->OnlineServiceSettings->ReplacePayeeNames....

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