Importing / Integrating Paypal with QuickBooks Desktop



hi this is Hector Garcia I want to show you how to work with PayPal transactions in QuickBooks Pro premier or Enterprise I will have an entirely different video dedicated to QuickBooks Online so this one is just for QuickBooks desktop for Windows Pro premier or Enterprise now I'm actually going to show you four methods that there's actually four major ways of entering PayPal transaction data into QuickBooks I'm going to show you the manual data entry mode which basically means treating a paper like a bank account and manually enter all the income and all the expenses there is using the copy and paste function of batch enter that it's only for the accountant edition of QuickBooks but you can download a CSV file then you can do some copy and paste technique so I'll kind of show you that I'll show you the traditional iaf file which is super fast but kind of messy and I'll explain to you why and then lastly I'll show you my the most efficient the best way of doing it which is using bank feeds and unfortunately you do have to make the investment into into buying a tool called to QBO convert pro but trust me it's it's way worth it it's under $150 and you can use it for unlimited times of limited accounts and it's gonna be very very useful and I'll show you the very end so you can kind of appreciate why I think that's my favorite way and you can use any other four techniques let me start with manual data entry so to frame things up when you go login to PayPal and usually the home page of paper will look like this or maybe it would look like classic mode and I'll show you what either one looks like and it really depends on how you have it set up in your your PayPal account but doesn't matter which PayPal account you have even whether it's classic mode or or the new modern mode you're gonna have to go into a place where it says history download history and then once you click on that the screen would look like this and the download history menu gives you a couple of options one is the day range ok that's just the easy one we're gonna download May 4 example and file type this is sort of the challenging one so the only three I'm going to focus in is comma-delimited completed payments and then we're going to look at the quicken and we're going to look at PDF all activity the QuickBooks one could also work but it doesn't work too well and that's really the reason why I'm making this video because the one QuickBooks option is not so good but what we'll cover all of them so I started with the comma delimited so I downloaded the file and I'll show you what that looks like and typically it looks like a spreadsheet so since we're gonna work on manual data entry what we would do is we would take this spreadsheet here and we would enter it into a paypal bank register sort of like this so there's my bender KOMO there's the dollar amount so I will just go into my chart of accounts for example make sure that I have a paypal bank account and then let me just look at this real quick that's me 31st son so I would just do regular QuickBooks that I entry at this point right so May 31st 15 the vendor is Como and as a vendor 119 don't remember the amount 97 and then office supplies right so that's kind of a point right which we would just go one by one transaction by transaction and we enter it just like it was a register and then we would reconcile it at the end okay that's kind of tedious it's not my favorite way of working especially when the number of transactions are a lot more than what you see on the screen here so that's the deal with manual data entry so if you're working with any version of QuickBooks Pro or premier and/or an older version of a counter or Enterprise you don't have much of a choice what you're gonna do is you're gonna do manual data entry now later on I'm going to talk about a conversion tool that allows me to convert some of these transactions into bank feats like that could actually supersede data entry so I'm gonna cover that after the fact so the second scenario is we're going to batch enter the transactions with batch enter this this only works with QuickBooks accountant or QuickBooks Enterprise 2013 or above and the concept is instead of manually entering these transactions we're basically going to copy and paste them into a module called batch enter again you have to have QuickBooks accountant Edition or Enterprise Edition so in a nutshell what we're going to do is we're going to split all the deposits with payments or the expenses so we're going to re sort this spreadsheet that we got from PayPal and we're going to sort that by it just to grow some out like a work and basically we're going to get a group that is all expenses that will be these and then we're going to get a group that is all income or deposits so I'm going to go back into QuickBooks and then that was the accountant menu by gender and I have basically two bones checks if we start with checks basically that means that we're gonna cover expenses now this is a little bit confusing for some people because these are not actual checks these are expenses coming from PayPal however in QuickBooks the word check basically means money coming out of a bank and and PayPal is treated as a bank for these purposes so we'll start with the checks now notice the pattern here the these columns were set up like this date to such a number pay account amount so we're gonna have to make sure that this columns here look very similar than our spreadsheet here so let's look at the data that we have here we have dates then their name we have the net which is really the number that we're looking for and we don't need anything else so you simplify things I'm gonna go ahead and delete all the columns I don't need that away just narrow it down to the information that I need now you get rid of all this stuff it's not really necessary it's a lot of extra information who bought from you their address all that stuff but for importing purposes we're going to put this information so there it is state name net or that's really what it means this amount so I'm going to go back into QuickBooks and make sure I have like columns look the same so we have date we grab name would be payee so I move that up and then I move a mount up okay number I don't even need that there's no check numbers and I'll hit okay so now that my columns look exactly how my spreadsheet looks I can come into my spreadsheet and I can copy all this information now remember I'm only focused on the checks here so copy needs I'm gonna come in to come in to QuickBooks and come in here and paste okay now one of the problems is that I can't paste negative amounts so I would have to have made sure that none of these are negatives in Excel we can do things like this multiplying something by a negative one and I would basically turn the negatives into positives so I can just copy and paste over these okay and that would basically turn that so there's obviously some cleanup work that has to happen in Excel before we do that so let me copy these and the number I'm just dealing with the checks at this point right all the expenses so I'll go back and paste this again okay and also there's all my amounts and the vendors don't exist I have to make sure that I create all these vendors so I have to make sure that every one of these vendors as I just click down there create perfect remember this is a these are expenses oh this is money coming out of my PayPal account so I'll choose whatever my categories are and then obviously this is the accounting portion I have to make sure that either this part okay so when I finish selecting all my my categories okay whatever they happen to be then I'm gonna go ahead and click on save transactions now QuickBooks is gonna ask me here are you sure they really cannot be undone you want to do this and we double check that it is in fact the PayPal account we hit yes perfect now we have to do the other part which are the deposits or the income so I'm going to switch here from checks to deposit and again I have to make sure that the columns look the same so we have date name and net so I'm going to come here in a transaction type deposits and then I'm going to go to customize columns and I'll be date name and then the amount is net here okay there's my three columns right go back into my Excel select only my deposit transactions right at this point I just remember those were the ones I'll copy this going to QuickBooks paste okay if the customers are not created at this point remember they're paying me right so in theory all these should be customers so quick got all these and then the account will probably be something like consulting income right and then I basically have to do the work of make sure I figure out and put the correct category here now one of the challenges here is that I'm importing income from PayPal but I'm not really separating the fee which is a problem right because the way that paper downloads there's no fee or the fee is netted from the transaction and also this technique does not work if I create invoices beforehand because and then I'm I'm gonna have an issue because all these invoices are gonna stay open so this is only useful when the data that's coming from paper is gonna be treated basically as cash basis income so I hit save and close okay and then one of the beauties about this is we treat this just like a bank account so I'll go banking reconcile I'll select here PayPal and then I know my ending balance of paper was zero it's I happen to know this and then I would select basically all these transactions right and then I'll have to double-check right there I got some duplicates here that can cause obviously some issues but you know basically the end goal is to go back into my paypal here and check transaction by transaction and reconcile these just like a bank now one of the problems here and it's at the primary reason why when I went to reconcile there's a difference it's because this spreadsheet here or I'm gonna just undo a undo this a bunch of times so we can sort of get back to this part of the spreadsheet look like the problem with the spreadsheet is that paper does not download bank transfers it only downloads deposits an income at least when it with when we download the CSV file we download the excel file so you're gonna have to in you know get the in the PDF version okay and then just give you some context let me go back here to my PayPal account one of the options that I have when I went to download was PDF so I always download the PDF that looks like this to make sure that that looks like a bank statement and I can follow along but notice that there's a couple of transactions here here we draw from bank account add funds from bank account these would have to be added manually you can't copy and paste these unfortunately so that is the concept of of batch entering but obviously we're missing out the transfer of portion now I mentioned earlier that the challenge with that technique is that everything imports fully however the the main problem is that the bank transfers don't come in and the logic behind that a lot of people sort of think well you know what why does some people give you an incomplete Excel spreadsheet with transactions and the reason behind that is because bank transfers of money going from your bank account into PayPal and so forth it is assumed that that information is going to be reconciled when you're doing your bank reconciliation your checking account reconciliation so they do that to avoid double entry so it actually is kind of logical that that happens the next technique is importing the iaf file so this is sort of the traditional method PayPal for a very long time has had this iaf file available for download and the nice thing about it it actually works in any version of QuickBooks maybe 20 2007 and above so we really don't have to worry too much about the specific version or whether you have a real new one or not but this is the messiest way to do this but it is the fastest way to do this as I mentioned earlier one of the options here on the paypal window would be this QuickBooks AF which I said earlier that you really shouldn't do that but I'm gonna show you anyway so you have an idea of how these functions so as I attempt to download the file the paypal windows can ask me where do I categorize all these transactions and it's asking me what is the name of the PayPal account this is actually asking about the paper bank account again mine happens to be the same name here and then it says where do I categorize income and where do I categorize expenses so these are the default options here or the income and other expenses so well we'll leave those as is because I'll show you kind of how those work after the files downloaded what I'm gonna do is I'm gonna go to the file menu click on utilities click on import click on IAF file then I'm gonna go find the file wherever it said that I downloaded it click on it and hit open now I said this is the fastest way because that was just literally three clicks the problem is it's a little bit messy so I'm gonna show you exactly what I mean by that when I go into my chart of accounts and then I go into my PayPal account although go click on my PayPal account you're gonna notice that everything came in actually that's not bad right it doesn't look messy at all there's income coming in income coming out but it looks pretty straightforward the challenge is that in my chart of accounts the system actually created a bank account called other expenses and a bank account called other income so I just have to make sure that I rename this to something like right PayPal sales right money that I'm making PayPal sales and then go ahead and put that on their income and then I'll put here up here and where's my other account was created yeah this strange one here I call this one PayPal expenses okay this is just sort of temporary entries and I'll show you exactly what I mean by that and I hit save and close so the the sort of the good things about this option is when I go into PayPal all the transactions come in same as the other options I don't get the bank transfer so I'm still missing the bank transfers which is one of the issues here but I do get all the all the money's in the money's out now when I go look at a profit and loss for example I'm gonna look at a profit loss and one of the problems that I have here is that I have consulting income I have PayPal expand or consulting income for a second let's just take a look at PayPal expenses and PayPal sales so one of the things I need to do is I need to double click on and let me just switch this to cash basis so we're not looking at any open invoices out there so what I need to do at this point is maybe pay PI cells and I'll double click on that you know maybe maybe I'm ok with this maybe I'm comfortable with all the money's coming in as with the gross amounts coming in here as a categorical paper ourselves but PayPal expenses I really really can't have a general category so I'm gonna have to go through each one of these and reclassify them so I have to go to each one and reclassify them which is kind of the issue however if you are working with the accountant addition then you know there are other ways to do this quite fast so if you're working with enterprise for accounting addition you know there's a real neat way to reclassify so I'll show you here real quick just just in case you you don't know about The Bachelor classified tool but I could actually do him here quite quick I can select you know maybe these three office supplies and I can do it a lot faster than one by one but let's say you don't have the accountant Edition and you can do this I'll tell you the good thing about the iaf import and me just go back into the bank register there so you go back into the paypal back register there we go the good thing I like about this import is that all the vendors could create it everything goes into a lump income account and a lump sales account which I can you know very easily change and then I have to do the renaming what I like about this I don't have to sit there and create vendors what I don't like it is a creates vendors for me so it could do a lot of duplication plus it actually doesn't create it on the vendor section it actually puts it under the other name section so one of the issues that I have now it didn't happen this time around because I really have created all these vendors but but if the vendor doesn't exist it would put them into other other names that's kind of a part of their own like let me now show you the option that I really like my favorite option so the reason I mentioned this is my favorite technique is for a couple of reasons reason number one is because I may have invoices in QuickBooks that maybe I want to match with my PayPal payments instead of having the the past is coming in straight into income so that's one of the main reasons the other reason I really like this option is because I love bank feeds I love online banking and I love using the bank rules to categorize expenses now the challenge is is that PayPal cannot export a bank fee they cannot do a dot qbo file but it can but it can export a quicken file called a qif file which is this one that I'm highlighting here and I do have to make the investment right and in getting a conversion tool that can actually go get the QAF file into a dot qbo file so that's one of the most important things here you have to kind of have that tool so I'll show you kind of how that works and the link down here tells you where you can download the trial of the tool and let me just minimize this for a second and let me run the tool to show you exactly how it works so just how well the the tool opens up I just want to give you some context what I mean by that quicken file is in this download history option I would go and click quicken that's that's what I meant by that I'm gonna download a quicken file and then as I mentioned I'm gonna use this tool called to QBO convert Pro and I click on settings here to kind of show you how this works then down here one of the options that it says it says you know separate splits this this talks about specifically the quiff conversion and the quiff comes with or the the qif quicken file comes with detailed information about income and the split so have to make sure that this option is selected here and also down here I have to kind of give it a bank name and unfortunately it's kind of hard to explain but PayPal is not a bank in part of the bank feed system so I just have to pick a regular bogus bank account like Chase or something like that so I'm gonna hit OK here and then I'm gonna click on the convert button and then I'm gonna go find my qif file which I just downloaded and there is there's a qif file you know what before I open that let me I want to open up the QAF file in in Excel because I like for you to kind of see what the QAF file looks like so you kind of have an idea of what is the information that's coming from from PayPal so this is the qif file I downloaded for PayPal I'm just gonna drag it here into Excel and this is what it looks like so this is kind of gibberish you know this is the information that the PayPal is sending me via this qaf file so so all trust me all the information that you need is there and it's not displayed in a really friendly format however once I'm get get through the conversion it's gonna work beautifully so let me go back into my to qbo convert pro which is the tool that I used to convert select my qif file and I'm going to click on convert to QBL and basically it's gonna create as I mentioned a dot qbo file so now I can go into QuickBooks and here's a blank register so you get an idea what that looks like and I go into banking bank feeds import web connect so once I click on that then I go find that qbo file I just converted so I'll select that and hit open and basically we're gonna get into familiar territory which is Bank feeds Bank feeds is the the traditional way that we get our banks on their credit cards connected into QuickBooks so things get downloaded so I'm gonna select my PayPal account here because I'm that that QBO file is a it's related to my PayPal account and then my bank feeds window will open up and then hopefully if you use bank feeds before this would be real familiar for you but the huge huge huge advantage I can I can't say this enough the huge advantage of this option is that if I happen to have open invoices so I'll just let me just go into my invoices real quick so let me show you I got some open invoices here right or maybe I have paid invoices it doesn't matter if I use the invoice payment process at all because I'm managing inventory or because I initiate the invoices in QuickBooks not in PayPal like the other techniques that I chose have the major problem that it just creates new income but the beautiful thing about Bank feeds is that for example you just take a look at these deposits here when you look at these deposits here and and for the event of these last three here there are invoices involved so I can actually click on this one and instead instead of actually creating a more income or new incoming to our accounting I can just click on select here click on add more details and then I can just select the particular open invoice and even if the invoice is that open if they happen to be a payment against it I can match it so I'll click on Add to QuickBooks I want to show you a similar example let's say that for example this this one invoice I received here for $2.99 40 and I'm gonna go ahead and and show it here so let me pull up that invoice there it is let's say the same voice has been paid for so I did go to QuickBooks and I clicked on receive payments that that was actually part of my standard workflow so I'm gonna go ahead and receive it and it goes it just goes into under positive funds right just a regular receive payment function here so I'm not gonna hit save and close but let's say I haven't done the actual bank deposit but I have received the payment so one of the great things about working with Bank feeds is that what once I'm in bank feeds I can actually here and this will actually be able to match to an existing transaction sorry I might click on add more details but I'm gonna be able to match it there so when I click on add more details I have my tab for my payments received or my undeposited funds and I have my tab for open invoices just like I showed you last time so I'm gonna go ahead and click on that click on Add to QuickBooks and then I'll go ahead and select this one too and because I actually have an open invoice on that one whoops that's the wrong button that I press there I'm gonna click on add more details select the open invoice and done so the great thing about bank feeds as well is that I even if I don't have everything on invoice like for example this one wasn't on an invoice and this one wasn't on an invoice I can still come in here and put it straight into an income account if I wanted to so I'm actually not completely stuck to have to use employees receive payment and the matching mechanism I could also just do direct data input but but the single most important piece of bank feeds it's a it's really expenses and Bank rules because when I actually started classifying these expense accounts and if you never use bank feeds before obviously this is a this is new to you but you use this before the beauty of doing everything through bank feeds is that when I when I add these this is gonna create rules so in the future in June July August whatever when this vendor show up again this classification will come in so this is a really real nice thing about that and again the name of that program is called to qvo convert pro this is really what it looks like and I'm gonna put here the link and I'll be sure to put the link down there in the in the comments if you want to go ahead and check on the trial thank you very much for watching and I hope that this was useful for you

10 thoughts on “Importing / Integrating Paypal with QuickBooks Desktop

  1. What about invoice items? My PayPal transactions include itemized details like a product ID, name, price, qty, etc. When I enter these manually in QB I create a Sales Order or Invoice, enter the line items accordingly, receive the payment against the invoice, and then record the deposit (with the fee included here to keep things balanced with PayPal activity log).

    These import options look fine, but I don't see anything about these invoice items. If I'm using QB to control inventory and generate inventory-based reports is there any way to automate that?

    Even information on how to generate a QBO file that includes line items would be great. Thanks!

  2. I know how to batch enter, and add vendors and customer in lists. I have the accountant edition, I am having a problem when I download the .csv, I have all the transactions from paypal, and it includes all the pending ones as well, and all the completed ones. When I remove the pending from the csv, the deposits and credits left over are not correct. I am having trouble getting the csv correct before uploading. The paypal csv gives you more transactions that it seems a bank account file would show. trying to learn how to clean up paypal csv an easy way.

  3. Hi Hector,
    thanks great videos and useful information. Please can you comment on the suitability of the tools and approach for multicurrency paypal – primarily tranactions are in USD but also GBP. Our reporting currency is GBP. Ideas on approach would be great (IIF file is as you say a disaster – it refuses to work or crashes Premier 2016 for me), so QBO is what you recommend but the Paypal download specifies "QBO – USD Only"

  4. Hector-I am interested in advice on putting transactions into QB premier desktop from paypal online sales transactions. It's important to enter single lines to record what was purchased. IS there a better way than line by line? Secondly, the paypal account has the paypal here and online sales all jumbled in one account, does it work to have two paypal accounts, one for counter POS transactions and one for online sales? Thanks so much!

  5. I have a client that has a credit card associated with her paypal is that treated as a credit card account or part of bank acct? Paypal is very confusing!

Leave a Reply

Your email address will not be published. Required fields are marked *