How To Import Transactions into QBO Using a Spreadsheet

Hello everyone. Today we're going to talk about how to upload transactions through the bank account.

And you might want to do this.

Because you have some expenses that you would like to record in QuickBooks Online, but you don't want to have to add each one manually.

So this can help you save a lot of time.

If you actually have a bank account, you can simply connect the bank account and have the transactions download automatically. But sometimes you spend cash or money out of your own pocket or a non business bank account. And so it's only really a handful of transactions. So let us talk about how to do that. So the first step is to create your file. And this can be in Excel or it can be in Google Sheets, any spreadsheet program that you can then save as what is known as a CSV file, a comma separated value file, which is the only type of file that QuickBooks Online accepts for an upload of this nature.

The three columns that you need to.

Have on your file include the date, the amount, and a description of the transaction. So I'm going to name these columns date, amount, transaction.

And you'll notice you can enter this.

However you want.

I have gone ahead and pre populated this spreadsheet with some transactions.

You can go ahead and do the same with whatever transactions that relate to your business that you want to upload to QuickBooks online.

So first of all, let's look at.

The date and the date needs to be in a date format. This is very important. You would go here and you would select, select the date format.

Every computer is different.

Mine is set up for month, day, year, which is the US system. But other computers will have year, month, day or month day or day, month, year. So it doesn't matter as long as it's in a date format. That's very important. The amount similarly should be be in a number format, not currency, not accounting, because this introduces other issues that may be problematic like brackets and commas.

The second thing you'll notice about the.

Amount is that they're all negative.

And the negative is to indicate that.

They are an expense.

QuickBooks Online may have updated this. They may actually ask you whether it's.

An outflow or an inflow.

But just to be safe, I would.

Get into the habit of making these negative amounts.

And finally you have a description and.

You can put in what the transaction relates to. You can put in a more detailed.

Description, which is always useful, or if.

It'S something very simple, just a simple description, that's fine.

So now that you have your spreadsheet.

Set up, you can go ahead and save this as a CSV file. So we'll choose CSV from the drop down and then click on save and.

You'Ll get this error message because a.

CSV file can only have one tab in it and you'll notice there's a bunch of tabs here. That's fine because this is the only tab that you are going to be uploading.

So click on ok. One note to make is that most banks will allow.

You to export your transactions to a CSV file.

I highly recommend doing that.

And then it is easier to manipulate. You can delete the transactions that don't relate, but at least you don't again have to enter them manually into an Excel file or QuickBooks Online.

Okay, so now let's go ahead and.

Initiate the upload upload process.

So if we go to Quick our.

QuickBooks online file and we go to bank transactions.

You'Ll see I have a cash account. You can also set this up while.

You'Re doing the upload. If you don't already have a cash account. It could also be called a personal.

Account, petty cash, cash on hand, whatever.

Makes sense sense to you. And I will show you how to set it up as well. So let's click on upload from file.

And here you will see we have our CSV file.

So let's click on that.

Let's click on continue.

And then we are going to select the cache count as indicated.

But.

But you can also add a new.

File directly here and you want to.

Make sure that it's a bank account.

Because it is an expense account.

You could also choose a credit card account if that's better for you. Because a credit card is typically shown.

As a liability and this is going to be an amount payable to the shareholder or the owner of the business.

I personally prefer a bank account because.

I like to see it in the bank and sometimes you receive money personally against an invoice that you want to put into the bank account as well. So it is a good tracking account.

Detail type Cash on Hand and the.

Name is Cash on hand. So that's fine and we'll move on to the next step.

So here you set up your file.

Is the for showing your file a header? Yes, we just added our header headers, the date, amount and description.

Here you can choose one column or.

You can choose two columns.

Two columns is when you have both positives and negatives. So this file that we're looking at could have a second column where we.

Have deposits that are coming in, and those would be positive. And QuickBooks Online would be able to upload both. You probably have to enter this in a separate. There we go.

So now your date format, as mentioned.

You choose a format that is present your computer.

In my case, it's month, day, year. So this is what I'm going to select.

So we've got our date. We are going to match the.

Transaction field.

To the description, which is what I named the column.

And then you also have the option to add a check number if you're making checks. Chances are, for the purposes that we're.

Doing this, you don't necessarily have checks.

So click on Continue.

And here is where QuickBooks now asks you if you want to reverse the value. So sometimes if you export this from.

Your bank, it will show up as positive values. It's very important that they show up as negative to go into the cash outflow or spend column in QuickBooks Online.

So this is fine.

We will select all of our transactions.

You may look at some of these transactions, say well, and decide not to.

Upload them for whatever reason. So that is an option. You can unselect a transaction and once this is done, click on Continue.

It tells you that it's going to import the number of transactions and click yes.

And you will see that your transactions.

Show up here in your cash account. And now you can go ahead and allocate them.

I have. When you look over here, you'll see.

That I've set up certain rules so when Hydro is mentioned, it automatically will allocate it to utilities. Similarly for Internet, it goes to computer and Internet expenses. And so I can simply review these and add them.

If I want to add everything with a rule.

The way that I would do this to save a little time is to.

Click on categories or matches. I click on the first transaction and you'll see this little black bar appears.

And this allows you to accept a batch of transactions.

So now I'm going to scroll down to everything with a rule, click on.

The shift key on my computer and.

Select.

All of these transactions. This is a very.

Effective way to save time in QuickBooks Online. I love this feature, so all of.

This looks good to me. You should review all these transactions, make sure that they're being correctly allocated, and.

Once you know you are certain that they are, click on Accept.

Now you'll see that I have four transactions.

So Office 365. This is a subscription, so I'm going to open it up. Just click anywhere to open it up.

And from here I can select Microsoft.

I can add a new supplier called.

Microsoft and just simply click Save here.

I don't need all of Microsoft's details.

In there and here for me, I'm going to put this to dues and subscriptions.

You could also put it to office.

Expenses or software expenses if you have that.

There's various ways.

In my case, the tax code is exempt, but you want to make sure Microsoft does charge sales taxes.

So you want to make sure you choose a sales tax code that applies to your situation. So click on Add and you would do the same thing for the rest of the transactions.

And in terms of allocating transactions, that.

Will be a tutorial for another day.

One other note.

If you do want to create rules or you want to check the rules that you already have, just simply go to Rules up here, here and click on Go to Rules. You'll see what you have and you can create a new rule either from here or directly from here, which is creating a rule over here.

Also something we will talk about at a later tutorial. I hope this was helpful. If you have any comments, please do not hesitate to leave them or ideas for new videos and I will be back soon with another video.

Have a great day.