Expenses

How To Import & Track Expenses Automatically for Maximum Productivity

Manually entering business expenses and other data into your accounting software can be extremely time-consuming, and when you’re entering information by hand, there’s always a risk that you may put in the wrong number, forget a decimal point, or make other small errors.

Luckily, you can save a lot of time and eliminate errors by automatically importing expenses and other business data directly into QuickBooks Online. The process is slightly different depending on what you’re importing to your accounting software.

Historic Credit Card and Bank Statements

Sometimes, you may need to upload old credit card or banking transactions into your QuickBooks records. For instance, if you switch to QuickBooks in the middle of an accounting period, you need to upload these old expenses into your records. Similarly, if you’re just getting started with QuickBooks and want to upload startup expenses that you incurred in the initial days of running your business, you may also need to upload old transactions.

In other situations, you may want to upload banking or credit card statements from previous tax years so you can generate reports comparing this year’s expenses or profits with last year’s numbers.

Regardless of why you want to import expenses from these accounts, you can typically connect QuickBooks to your online credit card or bank account and automatically upload the last 90 days of transactions. But if you want older transactions or details from a different time period, you can import the transactions using the following steps:

  1. Sign up for online access to your credit card or banking account.
  2. Open your online account and sign in.
  3. Request to download the history of your expense transactions into a comma-separated values (CSV) formatted file.
  4. Specify your time period.
  5. Customize the report. Some credit card providers have a series of default fields that you can’t change, as well as several fields that you can add if desired.
  6. Save the CSV file to your computer.
  7. Open QuickBooks.
  8. Go to Transactions and Select Banking.
  9. Select Upload Transactions. 
  10. Find the CSV file you just saved from your credit card provider or bank.
  11. Drag and drop or select files to manually upload your transactions, then select Continue.
  12. If you already have this bank or credit card account in your QuickBooks records, select the account from the dropdown menu and then select Continue.
  13. If you haven’t entered the account yet, select Add New, and then add your account.
  14. Make sure the columns from the import CSV file match the QuickBooks banking fields.
  15. Select Continue when you’re done.
  16. Review the transactions you imported and decide if you want to accept them.
  17. Select Continue once you have selected the transactions you want to import.
  18. Repeat for other files as desired.

QuickBooks can accept files up to 350 KB in size. If the file you get from your credit card provider is larger than that, try shortening the timeframe and downloading two or more files instead of just one. Also keep in mind that if the oldest transaction in your file is older than the opening balance date in your QuickBooks account history, the software will automatically change the opening day and balance in your QuickBooks records.

To continue uploading transactions into your cloud-based accounting software, just make sure your banking or credit card account is connected to QuickBooks moving forward. Then, the transactions should be synced automatically.

Expense Receipts on the Go

When you’re running a business, you often incur a lot of little expenses while you’re on the go. You may buy office supplies on the spur of the moment one day, take a client out for coffee the next day, and incur lots of other big and small expenses as well. If you’re paying with cash or using an account that’s not connected to your QuickBooks software, you need tools that can help you record expense receipts on the go.

Apps such as IQBoxy and Nexonia Expenses help you do that. You or your authorized employees simply snap a photograph of the receipt, and then these apps automatically upload the information to your QuickBooks accounting software. You can also snap photos of receipts and upload them automatically using the QuickBooks app.

Even if the expense is small, you still need to track it. Little expenses can add up over time, and if you don’t track those expenses, you miss out on valuable tax deductions. You also compromise the accuracy of your financial records. If you generate profit-and-loss reports or cash flow statements, for example, your records won’t be as detailed and accurate as they should be.

Of course, you can always save the receipts and upload them later, but if you take that route, you spend more time than you need to on this project. In contrast, if you upload the receipt instantly, you don’t have to worry about doing anything in the future. Once you get in the habit of capturing your receipts with a photograph, you can rest assured you won’t miss any of these expenses.

Cash Expenses

When you’re dealing with digital information, it’s relatively straightforward to sync data, but what about when you use cash? If you pay for something in cash and get a receipt, you can easily record the transaction using some of the apps explained above. In other situations, you can manually record the transaction with the following steps:

  1. Hit the plus sign at the very top of the screen.
  2. Look for the Suppliers section.
  3. Select Expense.
  4. Choose a payee from the dropdown list.
  5. Choose the Cash Account or select another expense category from the dropdown list.
  6. Enter the purchase date.
  7. Note cash as the payment method.
  8. Enter a description and the amount of the purchase.
  9. Detail any sales tax paid.
  10. Save and repeat for other transactions.

Remember that in the event of an audit, the Canada Revenue Agency (CRA) may want to see proof backing up your cash expenses. If you have a handwritten receipt, an invoice, or any other documents, you should keep those paper records in addition to your digital accounting records. The CRA requires small businesses to keep records for at least six years.

Customer and Supplier Names

Accounting software helps you track the numbers, but in a lot of cases, you need to anchor those numbers to additional details related to your customers or suppliers. For instance, you may want supplier details in your accounting software so you can note the businesses to which you’re issuing payments.

Similarly, if you’re sending an invoice to a client or issuing a receipt for a customer, you also need their details as well. Thankfully, QuickBooks has the ability to import that information automatically so you don’t have to spend hours keying customer or supplier names.

You can import these details directly from an Excel spreadsheet. You can also save a list of contacts from popular email programs such as Outlook and Gmail onto your computer, and then convert that information into an Excel file that you can import into QuickBooks.

To import a list of customers, suppliers, or even employees from Outlook, follow these steps:

  1. Select File.
  2. Choose Open and Export.
  3. Click on Import/Export to open the Import/Export Wizard.
  4. Select Export a File.
  5. Choose CSV as your file format.
  6. Find the folder where you keep your customer, supplier, or employee information.
  7. Keep in mind that you will need to import each of these categories separately.
  8. Follow the on-screen instructions to save the file.
  9. Save the file to your desktop so you can find it easily. Otherwise, make a mental note of where you save the file on your computer.

If you want to export contacts from Gmail, the process is relatively similar. First, you need to find your contacts. On the traditional version of Gmail, you click Gmail on the top left-hand side of the page. Then, you should see a short drop-down menu, with Contacts as one of the options.

You can find Contacts by hitting the Apps icon on the top right-hand side of the page. The Apps icon is nine little squares, and it’s next to the small bell that you select to get alerts. After selecting contacts, follow these steps:

  1. Click More.
  2. Select Export.
  3. Choose Outlook CSV and click Export again.
  4. Make sure to save the file somewhere you can easily find it on your computer.

Once you have a CSV file of your contacts, you can open that file using Excel. When you open the CSV file, you need to make sure the files you downloaded have the following fields in row one of each of the following columns:

  • A – Full Name
  • B – Company
  • C – Email
  • D – Phone
  • E – Mobile Number
  • F – Fax
  • G – Website
  • H – Street
  • I – City
  • J – State
  • K – Postal Code
  • L – Country
  • M – Opening Balance
  • N – Opening Balance Date
  • O – Business Number

You may need to delete, add, or move around columns from your original CSV file to make this possible. Even if you don’t have some of this information on your clients, you still need to leave the column intact. For instance, if your clients don’t have fax numbers, you still need Fax to be noted in column F of the top line of your spreadsheet, but you can leave that field blank for your contact. The opening balance and the opening balance date fields are also optional, but again, to ensure that everything goes smoothly, you should have those fields at the top of the spreadsheet.

QuickBooks does not import these fields, but the app uses them for mapping purposes. This means these fields tell QuickBooks where to put the data. After your sheet is organized, save it as an .xls or .xlsx file.

Now, you can finally start to import these details into QuickBooks. To do that, hit the gear icon on your QuickBooks dashboard. Then, select Import Data and choose Customers or Suppliers. Select Browse, and when the list of files pops up, choose the Excel file you just saved. Then, select Open, hit Next, adjust data as needed, and finally hit Import.

If everything doesn’t transfer in seamlessly, go back to your Excel file. Double-check that the information is in the right order. Get rid of any unwanted spaces. If possible, cut down on the number of characters. Some fields have restrictions on the maximum number of characters you can have, but the character limit is usually pretty generous so you typically don’t have to worry about it.

For instance, you can only have 255 characters in the city field, and most cities can fit easily into that character restriction.

Also, look over your formatting. As a general rule of thumb, names can’t have quotation marks or colons. Email addresses must have the @ symbol and a period, and they must not have any spaces. Websites need a valid URL format, meaning they must start with “http://www” or an equivalent such as “https://”. Generally, those are the only formatting requirements you need to keep in mind.

You can also import customer records directly from Gmail to QuickBooks without dealing with Excel. Note that this only works for customer records. You cannot use this tactic to import supplier or supplier records. Also, this technique doesn’t add the records permanently. It only lets you add these details to an invoice.

In QuickBooks, start a new invoice. Then, select Add New from the customer dropdown box, and choose Connect Your Gmail Account. Select Allow, and then complete the invoice as usual.

Inventory

You can import inventory records into QuickBooks. You may need to do this if you’re stocking a new line of inventory and have a spreadsheet listing the products from your supplier. You may also need this feature if you’re starting to use QuickBooks for the first time or are currently tracking inventory separately and want to sync everything with your QuickBooks records.

To import inventory records, start with your records in an Excel spreadsheet. Then, follow these steps:

  1. Click the gear menu on the top right of the dashboard.
  2. Select Import Data.
  3. Choose Products and Services.
  4. Select Browse.
  5. Choose the Excel file that has your inventory.
  6. At this point, a mapping screen appears. Make sure the QuickBooks Online fields match up to your file’s fields. For instance, SKU should line up with SKU.
  7. If anything doesn’t match up correctly, change the category using the drop-down menu.
  8. Hit next.
  9. QuickBooks will show you a preview of your entries. Make sure the sell, buy, and track boxes are selected.
  10. If you’re updating information, select the box that says “Overwrite all values for each product or service with the same name”.
  11. If you’re importing inventory data for the first time, don’t select the overwrite option.
  12. Correct any other errors in the preview.
  13. Click Import.

To double-check that the information has been imported correctly, select the gear icon at the top of the page, and select Products or Services. Then, look for the information you imported. If you hit edit while viewing the entry, you’ll see another page with all the information you imported. You can use this section to change or update these details as needed.

To ensure your inventory details import correctly, your spreadsheet should present information in the following order: Product/Service Name, Sales Description, SKU, Type, Sales Price/Rate, Taxable, Income Account, Purchase Description, Purchase Cost, Expense Account, Quantity on Hand, Reorder Point, Inventory Asset Account, and Quantity As-Of Date.

These labels should be in line one of your spreadsheet, and then you can enter the details for each specific inventory item under these labels in the corresponding columns.

It’s important to keep in mind that the purchase cost should be per item. The software automatically multiplies the purchase cost by your quantity on hand to give you the total value of all those particular items. The quantity as-of field is the date on which you recorded that inventory level.

If you don’t want to enter quantity, that renders the quantity as-of field useless. If you’re using zero as your quantity, you may want to put the first day of the tax year as your quantity as-of date.

Categorizing Transactions and Expenses

When you’re entering a single transaction, you can categorize it easily. You simply enter the information as usual. Then, you select a category from the dropdown list. Possible categories include cost of goods sold (COGS), subcontractor labour, sale of product, and dozens of others. To help you be as accurate as possible, there is also a variety of sub-accounts. For instance, if you’re entering a utility bill, you can choose the subcategory Electricity or Phone.

On the other hand, if you’re trying to categorize imported banking transactions, you can do that with the following steps:

  1. Choose Banking from the left menu.
  2. Select the row of the transaction you want to categorize.
  3. Add additional details as desired. For instance, if you want to add a payee to one of your expenses, you can click +Add and choose a payee from your list of suppliers and suppliers.
  4. Look at the category suggested in the Category dropdown menu.
  5. If the category is Uncategorized Income or Uncategorized Expenses, choose a more specific category from the list.
  6. If you set up classes or locations in your company settings, you can also select a class or location.
  7. After all the information is correct, hit Add.
  8. At this point, the transaction should move from the New Transactions tab to the QuickBooks tab.
  9. Save and repeat as needed.

In some cases, you may want to split an expense between a few different categories. To do that, you again start by selecting Banking and then choosing the row of the transaction. But this time, you select Split, which is toward the right side of the page.

This option splits the transaction into two lines. You choose a category from the Account list on the first line and enter the amount you want assigned to that account. Then, repeat this process on the second line to assign the rest of the expense to another account category.

If you need to divide the transaction between more than two accounts, select Add Lines. Then, repeat the above process on all the subsequent lines. If you make an error, you can delete rows by hitting the garbage can icon, or start over by selecting Reset. Finally, review your entries and select Save and Add.

Sometimes, QuickBooks takes transactions from your banking records and categorizes them as Transfers. If you can’t assign an account category to a transaction, check the radio button. If it says Transfer, change that to Add or Find Match. After doing that, you should be able to follow the steps above to categorize this transaction.

If you want to categorize multiple transactions at once, you need to pull up your list of Expense Transactions. If desired, you can filter the transaction using the drop-down Filter menu. Then, select the transactions you want to categorize by ticking the boxes on the left-hand side of the transaction lines. From the Batch Actions dropdown menu near the top of the page, select Categorize.

Choose the category you want from the dropdown menu that appears in the pop-up box. Again, you can select from a variety of Expense, Asset, COGS, or other accounts. Once you make your selection, hit Apply and save your changes.

When you categorize your transactions properly, you can create more reports. For instance, you can see your profits just by tracking your general income and expenses, but if you want to calculate your gross profit margin, you need to have your COGS expenses categorized separately.

Similarly, you can check how much you’re spending on utilities from month to month to identify areas where you can save money. When expenses aren’t categorized, you don’t have the fine details you need to make useful reports or detailed assessments of your expenditures.

If you want to switch from Excel accounting to something that’s easier to use and offers more features, it may be time to try QuickBooks . You can easily import bank transactions to QuickBooks online from Excel, and you can also use Excel files to import details about your inventory, customers, suppliers, and more. With QuickBooks Online, you can organize your business finances and stay ready for tax time.


Related Articles

Looking for something else?

Get QuickBooks

Smart features made for your business. We've got you covered.

Firm of the Future

Expert advice and resources for today’s accounting professionals.

QuickBooks Support

Get help with QuickBooks. Find articles, video tutorials, and more.