Small business tips and tricks: reconciliation hell (or 12,716 ways to spend your weekend) Mike | February 1st, 2010

Warning to readers: the topic of this week’s blog post is arcane, technical, and probably boring to roughly 95% of our audience. I hope that you “5-percenters” get something out of this and that the other 95% of you appreciate that what I am about to describe is how many entrepreneurs spend their Sunday afternoons in late January.

12,716 transactions in 2009. Separate transactions. Individual transactions. This is what we did in 2009 and now our accountants insist that each and every one of these reconcile our own data against our merchant processor data. OMG!

Why is this ridiculous exercise necessary? Well, they tell us that if we are ever audited non-reconciling payments will prove problematic. Problematic? Hello! Problematic is finding a way to do this that won’t drive me over the edge…

Alright, I exaggerate. There are ways to do this with relative ease. The key word being “relative.” Here’s what I did to ease my load and (fairly) quickly identify the problem transactions. Our merchant processor is PayPal. PayPal is great in many ways, but surprisingly awkward to use in many others. Every month I can download a report from their site which gives me a list of all of the transactions generated on our account for that month. Problem is that they do not make it easy to customize these reports, so you pretty much have to live with what they make available. OK – I can deal with that.

So I download roughly 1,000 transactions per month. Checking these manually, one by one, would literally take hundreds of hours, so I John helped me to create a new database report: “All transactions” is a simple list, which I can import into excel and that includes information such as the project name for each transaction along with date, time, amount, and (most importantly) PayPal transaction number. I simply import these into a spreadsheet one month at a time and I’m almost ready to go.

Next I took the PayPal transaction reports and did the same thing: imported them into another spreadsheet, month by month. Now begins the fun. If you have never used the Excel VLookup function, you may want to learn a bit about it. This powerful tool allows you to search across multiple spreadsheets to find matching information. By telling the “PayPal Transactions” spreadsheet to look for the matching transaction numbers in the “All Transactions” spreadsheet i can make excel return the name of the project associated with the PayPal transaction number. Simply copy the formula down the list and, voila! The project names magcally appear on the PayPal spreadsheet. Any transactions that don’t register a match display as “#N/A.” (BTW, what is up with Excel and these bizarre values that it returns? Why can’t it just say “error” or “no value?” Whatever, I don’t have time to teach Microsoft about user experience.)

Now I can simply sort each month’s data by the VLookup column forcing the #N/As to the top. Fortunately for me, there are probably only 5 or 10 for each month and this week I will attack those to figure out what they were. Here’s a link to a great post with step-by-step instructions for using VLookup. Take a look and see if this can help you to match up your own data, whether it is financial data, customer data, or just your collection of DVDs.

And I do hope that our accountants appreciate the effort. Dara? Are you reading this?

Need something designed? Name your price. Pick from 110+ entries. Love it or your money back.

Like our blog? You’ll freaking love our Twitter updates. Oh, and you’ll dig our Facebook page too.

  • TheNutz

    LOL
    This is too easy, tell us something harder :)

  • TheNutz

    LOL
    This is too easy, tell us something harder :)

  • The Accountant

    I believe that mastering VLOOKUP is the highest level of consciousness before reaching nirvana. Who knew that the Accountant would lead you to enlightenment?

  • The Accountant

    I believe that mastering VLOOKUP is the highest level of consciousness before reaching nirvana. Who knew that the Accountant would lead you to enlightenment?

  • The Accountant Partner

    Thanks for the reference. Hopefully this weekend you can spend your time in frount of the Super Bowl.

  • The Accountant Partner

    Thanks for the reference. Hopefully this weekend you can spend your time in frount of the Super Bowl.

  • TheNutz

    Funny guys, mastering match/index is the highest level of consciousness before reaching nirvana. :))

  • TheNutz

    Funny guys, mastering match/index is the highest level of consciousness before reaching nirvana. :))

  • http://www.contussupport.com Web Development Company

    Good to see someone else upgrade and it go smoothly with no errors. So appreciate this post for 2 reasons having the courage to upgrade and then to share it with us.

Hey, it's crowdSPRING!

Tens of thousands of the world's best and most successful entrepreneurs, businesses, agencies and nonprofits rely on crowdSPRING for affordable and risk-free custom logo design, web design, a new company name or other writing and design services. More than 162,000 designers and writers work on crowdSPRING. We create designs and names people love. 100% guaranteed.

Get Blog Updates

Free E-Books

12 Question Interviews with cS designers.
Get it »

Contracts for designers who hate contracts.
Get it »

Contracts for software developers who hate contracts. Get it »

More in Small business, Start ups (349 of 542 articles)

/** chartbeat **/