As many of you know, Hubdoc is one of my must-have apps for any client using any accounting software. The powerful fetch function allows me to work more independently instead of nagging my clients and waiting for copies of bank statements, online-accessible invoices as well as transactions imported through a number of methods including mobile app and email-in.
But that’s not where the fun stops. Not only does Hubdoc fetch statements, but also copies of e-transfers, cheque copies and CSV files. These CSV files come in handy when you are not able to connect a bank feed to QBO. But there are a few tricks to making these work efficiently. (For the following examples, keep in mind that I don’t know if some of these situations are specific to this bank, QBO or PC vs Mac.)
You can find the CSV files on the left side of the Hubdoc login beneath the accounts they are associated with. In this case, one CSV file belongs to a chequing account, the other belongs to a credit card account. To access the CSV files, simply click on the CSV folder you would like to access.
From the CSV folder, select the file you wish to download and select the Download button from the top right of the screen. Notice how the date format appears YYYY-MM-DD.You will need to open the CSV file to adjust the date format before uploading into QBO’s bank feed. To do that, I use the drop down arrow to the right of the download file and select Open. The CSV file will open in Excel.From the Office button, select Save As then scroll down to Other Formats. I like to change the name of file so it is easy to find later, but remember that it must always stay a CSV file format in order for the upload to work in QBO. Also notice that the date format has changed now that it is viewable in Excel and pay attention to the columns here and what data is in each column. Here we have date, description, credits (for credit card payments) and debits (credit card charges).You are now able to upload the file into QBO. From the navigation pane, select Transactions then Banking. Select the account you wish to upload the file for. You can see the account selected here is CIBC MasterCard. On the right side, use the drop down arrow beside Update and select File upload.Select the file to upload and select Open. Once the file has been confirmed as selected, click Next at the bottom right.Now map the file you just uploaded, to the account you want to compare it to in the Chart of Accounts. Then select Next at the bottom right.By using the drop down arrows, select the options to map the information correctly from CSV to QBO. However, the trouble comes when you go to select the date format. There are only three date formats given as options. None are based on the company file’s date format, and all three are a variation of “day, month, year” which the CSV file does not come in natively. To correct this, simply go back to the Excel file and change the date format to match the one you want to select in the file upload screen. I find it most effective to go straight to Custom date format as there aren’t any that match the default options in QBO. Then click OK.When you re-save the file, ensure you keep the CSV file format. Now that you know you need to change the date format, you can do it when you save the file the first time and before you upload it to begin with. You are now ready to upload the file (or re-upload it, if you forgot to change the date format first), and map it to the bank feed.Select Next in the bottom right corner. You will then be prompted to select which transactions that have been downloaded, you want to import. In most cases, it will be all of them. But this is a good spot to remove transactions for date ranges already reconciled. If you miss those at this step, don’t worry, you can always exclude the transactions within the bank feed later. Follow the prompts to complete the upload process. Once uploaded, the transactions will appear in the bank feed centre, and you will be able to add, match or even create and apply bank rules on them just as you do with transactions brought through a connected bank feed. It is a great alternative for Hubdoc clients who don’t want or can’t have direct access to their online banking through bank feeds.
Do you have any other ways you import bank data? Do you have other QBO questions? Join the discussion on Facebook in our QB-HQ Facebook Group – Your QuickBooks Headquarters for Tips, Tricks and Training.