Import Old Bank & Credit Card Transactions from PDF Statements into QuickBooks Online
QuickBooks Online (QBO) downloads the last 90 days of transactions when you first connect an account. This is great when adding a relatively new account. But what if you create a new QBO account for a business that has been open for many months or over a year that you want to "catch up" on? Some banks will only allow you to do download up to a certain amount of transactions: 90 days, 6 months, only a year. You can
to scan PDF statements into QBO, but that can be very expensive. It may be cheaper to just pay a bookkeeper to key in the data! But if you are looking for a free way, except for your time, of getting these transactions imported into QBO, I have a solution for you.
Download your bank or credit card statements in PDF format, if you don't already have them.
Open each statement using Nitro PDF Reader. It's free.
Convert to a text (.TXT) file. This should open up automatically in Notepad on a PC using the default settings.
Create a new Excel worksheet with columns: Date, Amount, Description.
Depending on the format of each bank or credit card's statement, you can usually start by pasting in groups into each column. In my test with Capital One, my text file listed the transactions in numerical sequence, but the date was together with the description separate from the amounts. So I just keyed in the dates manually from the pasted descriptions and formatted the column as *M/DD/YYYY. For the amount column, I formatted as a general number to remove the $ sign. But I had to create another column temporarily to change charges to negative amount (=B2 * -1), for example, and then copy down. Then, I copied and pasted only the values back into the original column. Payments and credits have to be positive amounts. Finally, I just highlighted and hit backspace to remove the sequence number and dates from the description, but it wouldn't hurt to leave them.
Save Excel file in CSV format.
Import into QBO using File Upload option in dropdown menu. Add the account or select the account if you already have it in QBO. It should recognize the Date and Description columns, but you may have to tell QBO which column to use for the Amount. Keep the selection for 1 column. Click Next and verify what will be imported. Click Next again and you're done!
I prefer to do one statement at a time copying the Excel template and saving for each month. That way, I can confirm the imported transactions show correctly in Banking after each import. And if I make a mistake, I can exclude and delete the last ones I imported, fix the file, and import again. But if you verify the transactions in Step 7 before the final Next, you should not have to do this.
Then, all you have to do is categorize and add or match to existing transactions, such as payments from a checking account already in QBO. You can speed things up by creating rules for transactions with similar descriptions so that QBO recognizes them and automatically adds them.
Save Attachments to Customers and Vendors in QuickBooks Online
I'm So Attached to You!
You provided the feedback and they listened! Yes, it's true. Using the gear icon in the upper right-hand corner of QuickBooks Online (QBO) there is an extremely important and useful feature under Your Company called
Feedback
. There is a dedicated team at Intuit that reviews feedback provided via this channel, buckets the most popular submissions, and then budgets the implementation of features slated for future updates. It's also a great place to provide
Praise
to the team for listening to us when our desperately needed features or fixes are implemented. So keep providing your feedback to make this product even better!
Attachments in QBO is a much needed function for a cloud-based software. Having the ability to snap photos from the mobile app or scan receipts saved as PDFs that can be attached to transactions in QBO is essential for record-keeping. Being a completely paperless business, I scan everything into specific folders in
, with my current year receipt folder synced to my desktop into which I use a scanner that saves PDFs. But that's a whole other process which I was go over in a later post!
One of the latest updates to QBO has been the addition of attachments to Customers and Vendors. When you edit a customer (or client, donor, or other type you have set up in the settings), there is now an attachments tab. Here you can either click in the box/icon or drag and drop files to attach one or more files. You can attach practically anything: contracts, Excel spreadsheets, photos, you name it. And there is a generous 25MB limit for all the stuff you can attach to one customer. I thought this limit was above and beyond what was need to attach just one PDF receipt to an expense, but being able to attach multiple files is necessary in a lot of cases, such as attaching contracts, outlines, photos, etc. to estimates or merchant processing receipts, delivery tickets, etc. to invoices and sales receipts, which can then selectively be sent to a customer via email. I could see how attachments could be useful for vendors too, such as adding sales tax exemption certificates, W-9s for 1099 contractors, and so forth.
Unfortunately, the ability to add attachments to Accounts wasn't added in this update. I have been asking for this option and consider it the most important since I would like to be able to attach PDFs of bank statements or credit card statements that have been used for reconciliations. Or maybe you would like to attach loan statements or bills of sale for long term liabilities or fixed assets. Hopefully, Intuit is working on this functionality. If not, please send your own feedback so that they know this is important enough to add sooner than later. Currently, I am just uploading PDFs to the Attachment center and then editing them to say "Statement" in the Notes. This way, I can at least sort by the Note column to find all my statements, but it is still a pain to have to do this along with sorting by Name to find a particular statement.
Send Invoice from QuickBooks Online using Images in Gmail
Save and Share Link
Intuit is constanting working hard and adding new features to QuickBooks Online (QBO) on a frequent (almost monthly!) basis. I have been really happy with sending invoices from within QBO as it allows my clients to enter a payment online either via credit card or bank draft, as I also have a QuickBooks Payments account linked to QBO. One of the great features of sending an invoice this way is the ability to view activities related to the invoice, such as any questions asked by me or my client and best of all, when my client has viewed the invoice (date and time!). Sometimes though, my client hasn't viewed an invoice, but this may be because they didn't even see it. And this can happen as the invoice sent from within QBO comes from Intuit's own email server with the email address "quickbooks-email@intuit.com". This has the potential of falling into one's spam email box. However, Intuit may have resolved this issue by
registering
this email address with service providers so that it doesn't get associated as spam. It could be that a client is just ignoring the email, knowing that it is an invoice just based on the subject. In any case, there is a solution.
There is a new option when saving an invoice in QBO called Save and Share Link.
This allows you to copy a unique URL that can be used anywhere you want. And when a client clicks on it, QBO still tracks the activity as if was sent via Intuit's email server. By clicking on the Copy link and close button, the URL is saved to the clipboard so that you can paste it later elsewhere.
I use
, so my primary email address uses my domain but I send and receive emails via Gmail. I could just easily past this URL into an email, but I think it is not really clear as to what page that URL redirects to, being so long and just a jumble of words and numbers. Therefore, I like to use images, such as buttons or pictures. But unfortunately, you can't just click on the image and add a URL, like you can with a line of text. You just get picture options.
There is a little trick to associate an image with a URL. Click on the image, hold down the left button of your mouse, and drag
ever so slightly
until the image is highlighted.
Then, you can click on the Link option in the editing toolbar at the bottom and insert a URL.
And now the image or picture is clickable and will take the user to the link so that they can view the invoice as if it came directly from QBO.
You can use whatever image you want, anything that will capture the attention of the recipient and make them click on it. And of course, you can put whatever subject you want in the email that you think will entice the recipient to open and view the email, like
Urgent
or
Important
.
Or course, you don't have to use Gmail to share the link, as you can use whatever email provider that you have. And it may be that you can insert a URL into an image much easier using other email providers. Or maybe not all, as what happened when I tried doing this in Yahoo Mail. It's been a while since I have used Outlook, so I am not sure how that will work there. But feel free to leave a comment based on your own experience!