Using Excel Macros to Manipulate QuickBooks Reports

Since I began working in accounting, I have observed some fellow accountants exporting data from accounting software programs to Excel and then performing repetitive tasks of reformatting the spreadsheet (moving data to other cells, entering calculated fields, changing fonts, resizing columns, etc).  This has provided me with the opportunity to use and share a valuable skill I learned in one of my classes in college so that these tasks can be completed automatically with the push of a button. One of the powerful features of Excel is Visual Basic for Applications (VBA), a programming language for creating macros.  A macro is a set of instructions for Excel to complete automatically that would otherwise be performed manually by a person. There are countless ways that you could use macros in Excel.  In fact, they can do about anything you would normally do except actually “think” for you.  Completing the tasks I described above of reformatting and manipulating data is just one simple example of what macros are capable of. The video below is a demonstration of a macro I wrote that I call “the trial balance formatter.”  It is designed to take an Excel export of a trial balance report from QuickBooks and automatically make the following changes:
  1. Combine the amounts from the debit and credit columns, show the amounts from the credit column as negative, and rename the resulting column “unadjusted.”
  2. Add four columns to the right of the unadjusted column for adjustments.  Columns C and E are for the amount of the adjustments and have a width of 12.  Columns D and F are for letters identifying the adjustments and have a width of 2.
  3. Add a column to the right of the adjustments titled “adjusted.”  Formulas adding the amounts in the unadjusted and adjustments columns are automatically entered into the cells in this column.
  4. Change the bolded account names and column headings to regular font.
Note: The data contained in this video is completely fictional.  You may want to make the video appear in full-screen mode to better read the text.  If you can’t see the video below, click here. Accountants: If you’re looking to add to your Excel skills, I recommend learning how to write macros.  I don’t write macros very often, but my coworkers and I use macros I’ve written every day. Clients/Potential Clients: If a macro could help you save time by automating a repetitive task in Excel, please let me know!

Tags: , , , ,

Trackback from your site.

Comments (10)

  • Avatar

    Andy

    |

    Dustin,

    Very good of you to provide this site, and to share the benefits of your labor.

    Thanks

    Andy

    Reply

  • Avatar

    Dustin Wheeler

    |

    Andy,

    You’re welcome, and thanks for adding me to your blog list on your website.

    Dustin

    Reply

  • Avatar

    David

    |

    Dustin:

    I am an accountant and am encouraged by what you said here. I am going to do it at work for my company. Many thanks.

    BR,

    David

    Reply

  • Avatar

    Brittany

    |

    Hi Dustin –
    Quick question for you regarding this. When I export a report from my accounting software (Quickbooks), it launches Excel by itself, and apparently does so without opening the hidden Personal Workbook where my macro is stored. Any guess as to a setting or something that I need to change?
    Thanks!

    Brittany

    Reply

  • Avatar

    Dustin Wheeler

    |

    Brittany, I launch Excel first, then export the report from QuickBooks. Hopefully that works for you!

    Reply

  • Avatar

    Todd Wheeler

    |

    Dustin: Talk about ironic, we have the same last name, I recently passed the CPA exam and I’m writing an Excel macro to manipulate Quickbooks data!

    Reply

  • Avatar

    Dustin Wheeler

    |

    That’s awesome, Todd! Congratulations and good luck with your macro!

    Reply

  • Avatar

    Mike McNamara

    |

    Hi Dustin:
    We have some clients that use our website to upload a csv file into our Accounts payable system. The csv file is formatted with certain cell formats with the date and a few others so that when our script runs it knows how to validate the fields properly. We have some large customers that use QuickBooks and I want to see how I can help them use Excel to import their data into our csv file without all the repetitive steps. Would you have any idea how I can do this?

    Reply

  • Avatar

    Tyler

    |

    Is there a way to create a macro that will automatically pull certain memorized quickbook reports?

    Reply

Leave a comment