Excel->Quicken macro

[Z Man]

I've made this request every few months, but never got an answer...

Does anyone have an updated macro to convert Excel 2002 files to Quicken
2002 (.qif) files? I had a very old macro that made this conversion, but it
no longer works under Office XP.

I'm not thrilled at the prospect of entering 60-70 checkbook transactions
per month manually, so any help will be much appreciated.

[Vasant Nanavati]

Hi:

>>I've made this request every few months, but never got an answer...<<

Most likely that means no one has an answer!
--
Regards,

Vasant.

"Z Man" <z1z@hotmail> wrote in message news:eVoN5lPkBHA.1164@tkmsftngp03...

[David McRitchie]

Suggest you look through Google Usenet for
search for all: quicken excel qif checkbook
look at all of the threads. You will need a programming solution
so will have to install a macro. The other way is to get a bank
that will provide the Quicken check information for you for free.

http://groups.google.com/groups?q=quicken+excel+qif+checkbook&num=100

Infomation on searching Google Usenet
http://www.mvps.org/dmcritchie/excel/xlnews.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Vasant Nanavati" <vas...@aol.com> wrote in message news:#P4GtuPkBHA.2112@tkmsftngp05...

[Z Man]

"Vasant Nanavati" <vas...@aol.com> wrote in message
news:#P4GtuPkBHA.2112@tkmsftngp05...

> Hi:
>
> >>I've made this request every few months, but never got an answer...<<
>
> Most likely that means no one has an answer!

I thought perhaps someone would volunteer to write an updated macro :) I
doubt that it is particularly difficult, compared so some of the answers
I've seen here.

[Harald Staff]

Probably not a technical problem if a macro could do it before. But consider
the possibibity; maybe no one here use quicken and have no idea of / info
about what such a file looks like ? Try providing some information on this
and see what happens.

Best wishes Harald

"Z Man" <z1z@hotmail> wrote in message news:O3ym6qTkBHA.1592@tkmsftngp02...

[Dana DeLouis]

Don't have an answer, but you may want to do a File - Export to look at the
general structure of the .qif format.

For additional reference...

General structure of Quicken Interchange Format (QIF) files
http://www.intuit.com/support/quicken/2002/win/1178.html

--
Dana DeLouis Windows Me & Office XP

"Vasant Nanavati" <vas...@aol.com> wrote in message
news:#P4GtuPkBHA.2112@tkmsftngp05...

[Myrna Larson]

I have a macro, but I haven't volunteered it because I wrote it to move
investment data from my original Excel databases to MS Money.

As such, (a) it is for investment data, not bank account data, (b) my XL file
contains data for multiple accounts, and (c) I use extra columns on the right
to reformat the data as required for the QIF file.

The macro writes to several QIF files at once and gets the actual QIF data
lines from the worksheet. The macro doesn't do the 'hard' part of creating the
QIF data lines.

It would be more work (to revise it for checkbook info) than I have time for
right now.

BTW, there is information on the Intuit web site as to the format of QIF
files, the field identifiers, required date format, etc.

On Sun, 30 Dec 2001 20:32:20 +0100, "Harald Staff" <harald...@eunet.no>
wrote:

[Z Man]

"Myrna Larson" <myrna...@chartermi.net> wrote in message
news:b27v2ucmh73eggvd0...@4ax.com...

>
> BTW, there is information on the Intuit web site as to the format of QIF
> files, the field identifiers, required date format, etc.

The data file format is certainly not a secret. All you have to do is
examine the .qif text file with Notepad. Here's a sample:

^
D03/23/2001
T-174.95
N15216
PMICHAEL B SAUL DMD
Mfinal payment
^
D03/26/2001
T5,000.00
N391
PTRANSFER FROM INSURED MONEY MARKET
^
D03/26/2001
T-39.62
PVERIZON PHONE BI PAYMENTS
^
D03/26/2001
T-387.86
N15223
PCITIBANK-MASTERCARD
MLisa's credit card

So, evidently, there can up to five lines per record. The Date line is
proceeded with a "D", amount is preceded by a "T", check number is proceeded
by an "N", payee is proceeded by a "P" and memo is proceeded by an "M".
There can also be a category, but I complete that field after importing the
data into Quicken

So, the macro would have to create five lines from the five Excel columns,
proceed each line with the correct identifier, and insert a ^ in between
records.

[David McRitchie]

I expect that you still have not looked at the references
mentioned in Google? One of the threads also shows
sample data.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Z Man" <z1z@hotmail> wrote in message ...

[Z Man]

"David McRitchie" <dmcri...@msn.com> wrote in message
news:uUhnhzYkBHA.1820@tkmsftngp07...

> I expect that you still have not looked at the references
> mentioned in Google? One of the threads also shows
> sample data.

Actually, I did look at it...

[David McRitchie]

summary: creating a Quicken QIF file for data transfer
from Excel to Quicken

Okay I guess the usual direction is from Quicken -to- Excel
Place the one character titles on the first row: D T N P M ^
This should provide flexibility for creating other QIF files.

The following Subroutine will generate the lines in a flat file
in the manner you described. It will handle up to 10 columns
which may work for other QIF files. The subroutine uses
.text instead of .value so it will put out to the flat file
exactly what you see in Excel, particularly done for
dates and numbers. If you show commas you will get commas.

Sub XL2QIF()
Dim Rw As Long, Clm As Long, filename As String
filename = "c:\temp\my.qif"
Close #1
Open filename For Output As 1
For Rw = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
If Cells(Rw, 1) = "" Then GoTo done
Print #1, "^"
For Clm = 1 To 10
If Cells(Rw, Clm) <> "" Then
Print #1, Cells(1, Clm) & Cells(Rw, Clm).Text
End If
Next Clm
Next Rw
done:
Close #1
End Sub

You won't see the exact cell separation in this, I did run
a macro to make column D with descriptive names more readable.
http://www.mvps.org/dmcritchie/excel/proper.htm#proper
it won't help with DMD or AT&T but you can change the
proper macro for your own use for those that occur frequently,
or run the macro as is, or leave them as unreadable CAPS if you want.

D T N P M ^
03/23/2001 -174.95 15216 Michael B Saul Dmd final payment
03/26/2001 5,000.00 391 Transfer from Insured Money Market
03/26/2001 -39.62 (blank) Verizon Phone Bi Payments
03/26/2001 -387.86 15223 Citibank-Mastercard Lisa's credit card

generated from the above which was created from your data and
description of what you would have in Excel to generate the
file below which looks like your wanted output file.

^
D03/23/2001
T-174.95
N15216
PMichael B Saul Dmd

Mfinal payment
^
D03/26/2001
T5,000.00
N391

PPtransfer from Insured Money Market
^
D03/26/2001
T-39.62
N
PVerizon Phone Bi Payments
^
D03/26/2001
T-387.86
N15223
PCitibank-Mastercard
MLisa's credit card

HTH, I trust that you will tell us of any corrections you
had to put in so that others benefit as well.

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Z Man" <z1z@hotmail> wrote in message news:#L1Lp5YkBHA.2712@tkmsftngp02...