Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Money Macro
This VBA macro steps through Excel and reformats the data in a way that makes it easy to import into Access so that you can use it to build a cube.
Russ Whitney
June 16, 2003
2 Min Read
To use the Visual Basic for Applications (VBA) macro I created to work with either the Microsoft Money sample file or your own financial data, open the "Downloads" file above. Open the Sample.xls file, and in the Microsoft Excel Tools menu, select Macro, Visual Basic Editor. Inside the editor, right-click the project on the left called VBAProject (Sample.xls), and select Import File. (Note that if you click Export To Excel in Money, which launches Excel with the Sample.csv file, then go into VBA, you'll see the project name as Sample.csv.) Next, select the MSMoneyHelper.bas file. After you import the macro, open the Visual Basic Editor's Run menu, and select Run Macro. Choose the macro named ProcessMoneyData, and click Run. After the macro has finished executing, you can save the Excel file as Sample.xls and import it into Microsoft Access.
If you don't have the VBA macro that I created or you want to use another extraction, transformation, and loading (ETL) method to perform similar steps, here are the specific formatting tasks that my macro performs:
Delete the first six title rows at the top of the file.
Reformat column B in date format.
Insert three columns in front of the Amount column and name them Subcategory, Action, and Security.
Begin stepping through the rows of data, and perform the following steps:
If the row is blank, delete it.
If the row is part of a split transaction, duplicate the date, payee, and account from the previous row of the same split transaction.
Delete the first row of a split transaction. (The first row is just a subtotal of the subsequent rows. If you import the first row, you'll be duplicating data.)
Split the category field into category and subcategory and separate them with a colon.
If the memo contains information about an investment transaction, create separate action and security fields.
Fill any empty fields with "(none)."
Delete the last two rows of the report because they don't contain valid data.
I'm not a VBA expert, but I do know an important trick that you can use for creating VBA macros: If you want to know how to perform an Excel task in VBA, pull down the Tools menu and select Macro, Record, New Macro. Perform the task that you want to record in Excel and end the macro. You can then enter the Visual Basic Editor and inspect the macro that Excel created.
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
