====================================================================== Microsoft(R) Product Support Services Application Note (Text File) WM0502: TRANSFERRING DATA FROM MICROSOFT EXCEL 3.0 AND LATER TO MONEY ====================================================================== Revision Date: 10/92 Disk Included The following information applies to Microsoft Money versions 1.0, 2.0, and 2.0a. -------------------------------------------------------------------- | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY | | ACCOMPANY THIS DOCUMENT (collectively referred to as an | | Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY | | KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO | | THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A | | PARTICULAR PURPOSE. The user assumes the entire risk as to the | | accuracy and the use of this Application Note. This Application | | Note may be copied and distributed subject to the following | | conditions: 1) All text must be copied without modification and | | all pages must be included; 2) If software is included, all files | | on the disk(s) must be copied without modification [the MS-DOS(R) | | utility DISKCOPY is appropriate for this purpose]; 3) All | | components of this Application Note must be distributed together; | | and 4) This Application Note may not be distributed for profit. | | | | Copyright 1991-1992 Microsoft Corporation. All Rights Reserved. | | Microsoft and MS-DOS are registered trademarks and Windows | | is a trademark of Microsoft Corporation. | -------------------------------------------------------------------- The information in this application note will help you save data from Microsoft Excel versions 3.0 and later to a file format that can be recognized by Microsoft Money. The conversion macro provided on the enclosed disk is a generic macro designed to allow you to convert Microsoft Excel data to Quicken Interchange Format (QIF). Unfortunately, we cannot customize the macro code to your specific needs, and we support the use of the macro ONLY as described in this application note. Requirements for Exporting Data from Microsoft Excel to Money ------------------------------------------------------------- Before Money can import data, the file to be imported must be in QIF. Therefore, before exporting data from Microsoft Excel to Money, you must first run a conversion macro that saves the data in QIF. This conversion macro, XLSTOQIF.XLA, is on the enclosed WM0502 disk, and instructions for converting data are below. Before you convert Microsoft Excel data to QIF, you must make sure that the data has been set up in columns. Step 5 of the procedure below shows several accounts set up in the column format required. Note that the accounts must be separated into groups. For example, all "Checking" accounts are grouped together, and all "House" accounts are grouped together. To do this, sort the entire worksheet by the Account Name column. Refer to the "Microsoft Excel User's Guide" for information on sorting data. You must have your accounts separated into different groups because you can import only one account at a time into Money. For instance, if you have three different accounts--Checking, Savings, and House--you must import three separate times. To Convert Data from Excel to QIF --------------------------------- 1. Copy the add-in macro sheet XLSTOQIF.XLA from the enclosed WM0502 disk to your hard disk. If you want the macro to automatically load when you start Microsoft Excel, copy XLSTOQIF.XLA to the XLSTART subdirectory of Excel. Otherwise, copy XLSTOQIF.XLA to any directory on your hard disk. 2. If you did NOT copy XLSTOQIF.XLA to the XLSTART subdirectory, choose Open from the File menu, specify the path to the macro, and open the macro. 3. Open the data file you want to convert to QIF. 4. From the File menu, choose Convert Data To QIF, which the conversion macro has added to the File menu. The Convert Data To Quicken Interchange Format (QIF) dialog box will display. 5. Provide cell references in absolute notation (for example, $A$1) for your first account. For example, to convert the Checking account information from row 5, as follows, A B C D E F G ------- --- -------- ----------- ----- ------------- -------- 5 9/5/91 225 Checking Bob's Video Movie Entertainment ($10.00) fill in the information in the Convert Data To Quicken Interchange Format (QIF) box as follows: Column Starts in Cell: ---------------------- Transaction Number: $B$5 Transaction Date: $A$5 Transaction Payee: $D$5 Transaction Memo: $E$5 Transaction Amount: $G$5 Transaction Cleared: Transaction Category: $F$5 Row Starts in Cell: -------------------- First Transaction: $A$5 Last Transaction: $A$7 If you press the ESC key, the macro will terminate and you will have to start over. 6. Repeat steps 4 and 5 until you have converted each account. Make sure you change the name in the Save Data As box for each account so the QIF files don't overwrite one another. By default, the macro will save to a file called FINANCES.QIF. In this example, you could name the files as follows: CHECKING.QIF SAVINGS.QIF HOUSE.QIF To Import QIF Files into Money ------------------------------ 1. After you have converted each account in Microsoft Excel to QIF, start Microsoft Money. 2. You may want to create a new file for storing your account information. For instructions on creating a new file, refer to the "Microsoft Money User's Guide". 3. If you converted three accounts in Microsoft Excel, you will need to create three accounts in Money for importing the data. For instructions on creating a new account, refer to the "Microsoft Money User's Guide." 4. Open the account you want to import the data into, and from the File menu, choose Import. Specify the path to the QIF file, select the filename, and choose the OK button. 5. Highlight the account you want to import into and choose the Continue button. 6. Repeat steps 4 and 5 until all your accounts have been successfully imported. For further information on importing QIF files into Money, refer to the "Microsoft Money User's Guide."