17 Nov2009
Archive for November, 2009
10 Nov2009
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:- Combine the amounts from the debit and credit columns, show the amounts from the credit column as negative, and rename the resulting column “unadjusted.”
- 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.
- 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.
- Change the bolded account names and column headings to regular font.
02 Nov2009
RSS has Changed How I Use the Internet
Over the past few months, I’ve been much more efficient in reading news, blogs, and other websites that interest me. I now read twice the content in half the time! In this blog post, I’m going to give my secret away.
You might have noticed a little orange button on some websites like the one on the right. I had seen them for a long time, and always wondered what they were. It is a logo for RSS (“Really Simple Syndication”) which is a web feed format used by many websites that publish updated content (most commonly for news and blogs). Unless you’re already familiar with RSS, that last sentence probably didn’t make any sense. Keep reading. It will. I didn’t really understand it until someone showed me how a “feed reader” works. That’s where I’ll go next, but first, a little background: Before I figured out how RSS works, I used to go to each of my favorite websites periodically to see what’s new. This process consumed a lot of time. First, I had to find the website in my favorites list in my web browser (and if I didn’t save it as a favorite, I had to do try to remember the web address or search for it in Google). Then, I had to look for the most recent content, read the title (and maybe the first few sentences of the article) and test my memory to see if I had read it before. This might not sound like too much work, but when you do this with ten to twenty websites, the time really adds up. Some websites offer e-mail updates for new content. This became annoying as I started getting my e-mail on my phone. I got tired of being interrupted by the new e-mail notification sound only to find out that the e-mail was reminding me to read a new article at some website. A feed reader (also referred to as a feed aggregator) brings all of the new content from these websites into one place, like your own personal newspaper. It also lets you know which articles you have or haven’t read. It frees up your e-mail as strictly a device for communication. Another benefit of using a feed reader is that you won’t see nearly as many advertisements, if any, compared to visiting each individual website. There are many feed readers out there, and most of them are free services. My feed reader of choice happens to be Google Reader. If you’re still a little confused about how feed readers work, watch this video about Google Reader in plain English. With the help of Google Reader, I’m able to keep up with important publications in the profession such as the Journal of Accountancy, read the local newspapers, and enjoy posts from other blogging accountants. I can also “share” items that I like and want other people to read. On the right sidebar of my blog, you’ll see a list of items that I have shared from Google Reader. One last thing … and it might be self-promotional, but I wanted to point it out in case you missed it. On the top right corner of my page, you’ll see a little orange button and the words, “Subscribe to my Blog.”