I imported PayPal Banking transactions using a CSV, however none of the Fee amounts appeared in Manager - only the Gross Amount. How can I import the PayPal fees which are a business expense?
Are there any other formats you can use to export data from Paypal?
Alternatively, send your CSV file to
firstname.lastname@example.org to reproduce the issue.
I was just trying to import last month’s transactions from a PayPal CSV file and encountered several new issues. I received messages saying that the “Description” and “Amount” columns were missing, so I renamed the “Item Title” and “Gross” columns.
The file then imported, however the “Fee” was missing, the “Transaction ID” didn’t import into the “Reference” field, the customer “Name” didn’t appear in the Payer field and the date came in as DD/MM/YYYY instead of MM/DD/YYYY. I looked at a couple previous PayPal CSV’s and couldn’t find any differences in format from today’s.
This had been working since an update last November to recognize Fees in PayPal statements.
Could you send me both, the old CSV and new CSV? I don’t think this would break on its own.
Thanks! Working great now.
This issue seems to have resurfaced again. About 2-3 years ago, I asked for help regarding the import of a PayPal statement into a cash account. A change was made in which the import separated my Gross Amount and Fees. All I had to do was ensure that the spreadsheet had column headings that included “Name”, “Date”, “Transaction ID”, “Type”, “Gross amount”, “Fee” and “Net”.
This was working great for the past 2+ years, however as of an update within the past 2 weeks (since 7/7), at least the “Gross amount” and “Fee” bank rules are no longer recognized. Would it be possible to restore that feature or be given access to an older Windows download?
You don’t say in your latest post how you are trying to import (what format). But your earlier posts centered on CSV files. The current Guide says that CSV imports recognize these only columns:
It’s been that way since at least August of 2014, well before you first raised the topic. To my knowledge, there haven’t been any changes. Your post implies that Manager was separating and properly posting gross amount, fee, and net. But I have no idea how that could have happened, since bank rules only support posting to a single account. So if you want help, you’re going to have to post much more specific information, including screen shots.
A feature can’t be restored that was not there, at least as you’ve described it. And access to older versions is never provided. Your data files will now not work with older versions anyway because of database structural changes that are accomplished automatically when you first open with a newer version.
I communicated the issue with lubos via email a couple years ago when I asked for assistance with the import of PayPal CSV files to my cash account (Import Bank Statement function). From your comments, it seems as though it was an undocumented feature, however, be assured that I have been using it regularly since then – at least every two weeks. It last worked shortly after 7/7/2017, but stopped when I updated the software today.
As the posts above from May 2016 shows, the PayPal import had stopped working last year, but was quickly resolved when I brought it to the attention of lubos. The screenshot shows the bank rules I have been using for quite a while that are now unrecognized by the software when doing my usual import.
Your screen shot doesn’t show your bank rules; it shows a list of your bank rules.
To tell more, you need to open the edit screen for the rules that don’t work and show screen shots of those. Then you need to show a screen shot of the CSV file you are trying to import. My suspicion is that PayPal has changed its output in some way and the old bank rules don’t recognize it anymore. As I said before, I know of no changes to bank import portions of the program in recent weeks. And no one else has complained of problems. (There are many PayPal users doing imports.)
Also, have you tried .qif and .iif formats, both of which are available in PayPal and supported by Manager?
Another screen shot is below (customer name changed). It was the direct result of a fairly recent PayPal CSV import into a cash account. Again, the column headings on the CSV used were “Name”, “Date”, “Transaction ID” (Reference), “Type” (Description), “Gross amount”, “Fee” and “Net”.
Since I use bulk bank statement imports rather than sales invoices, this feature has been incredibly helpful for me. I had assumed other PayPal users were using it as well.
@Tut, I sincerely appreciate you taking the time to assist, but wonder at this point if it might be easier to have lubos address it. When I first communicated with him, I sent him a sample PayPal CSV and he apparently used those column headings to work his magic.
To avoid unnecessary complication in my most recent posts, I referred to the CSV file as my PayPal import file. Actually, nearly all of my sales are now through an Etsy storefront and Etsy handles both my credit card and PayPal sales receipts. PayPal accounts for only 15% of my sales payments. The rest are credit card transactions processed by an Etsy vendor.
All my current sales transactions are now downloaded from Etsy in CSV format - the only option. Customer payments by credit card and PayPal are mixed together in the spreadsheet, but clearly identified. Some of Etsy’s column headings differ from PayPal’s, but, before doing an import, I manually change the headings to match that of the PayPal’s CSV file that was originally sent to lubos. A screenshot showing the CSV column headings that I have used for the past two years is below.
Once the import into my cash account for checking is complete and the bank rules have been applied, I look for any PayPal payment transactions and manually allocate them to the cash account for PayPal. This import process has worked great for the past two years and I would hate to lose this invaluable feature.
The following thread also refers to splitting PayPal sales and fee amounts using bank rules…
I can assure you, whatever he did was not just for you. If he added something to the program, it is there for everyone. I ran some tests, and it appears he did, in fact, add the capability to pull out separate gross and fee amounts from .csv files. That capability is still in the program as of version 17.7.41. And it works perfectly.
Here, you highlight the difficulty when correct, detailed information is not provided. I spent time downloading and importing PayPal .csv, .qif, and .iff files to try to spot your problem. Now you reveal you aren’t even dealing with PayPal. And you haven’t said when you started using the Easy downloads instead. A cynic would guess it might have been when you started having trouble.
I don’t intend to sound rude, but you are putting a lot of faith in some “magic” you think @lubos performed, but have shared nothing about what it was. Perhaps you don’t know. But you also haven’t explained in sufficient detail what your exact process is for taking advantage of this “magic.” Yet your assumption is that Manager has somehow failed. Given that no changes have been announced regarding imports, and my tests showing the import feature still working, it’s far more likely the change occurred elsewhere, producing a disconnect with your bank rules–the one thing about which you have shared nothing.
In an attempt to help, I asked for screen shots of the file you’re trying to import and the bank rules you are applying. Instead, you showed a receipt you claim was created from a PayPal CSV import (although you admitted later it wasn’t) and a bit of a spreadsheet that may or may not be related to transactions giving you problems (you didn’t say).
Please understand that to diagnose why you are having difficulty, we need to see the exact file you are trying to import and the exact bank rules you are applying, not descriptions of them or simulations or manipulated versions of them. It would also help if you could post an old CSV file that imported correctly (if you still have one).
Although I still appreciate you looking into this, the hostile tone of your posts are not. Never have I been rude or disrespectful. For at least two years, after sending lubos a sample PayPal CSV file and asking that the amount and fees be separated, I have been importing spreadsheets with those same headings into my cash account. When I reported that there must have been an update since July 7 that caused my bank statement to not import properly, as also happened about a year ago, you say this…
As an earlier post by lubos shows, that feature “was” there. (link in previous post above)
Prior to starting my business, I have been a corporate software engineer with over 20 years experience. In the US, the term “work your magic” is not meant to be taken literally. It is meant as a compliment to a developer or engineer who is able to solve a technical issue. So, I make a comment stating that lubos worked his magic, but then I get this…
No, I don’t know what he did, because he didn’t say and I don’t have the code. But he managed to split the amount and fee during the import and I was enormously grateful. Something happened in a recent update which broke that - at least in my situation, which is the whole point of my post - both now and in May 2016.
I already said that the current issue started since about 2 weeks ago (my last successful upload). I hope you don’t really think I just started using an Etsy download file this week, expected it to match PayPal and complain that it didn’t? Since Etsy processes both credit card and PayPal transactions, and their CSV downloads include both, I began using their file as the source of my import in the Fall of 2016. Since then, every week or two I do a bank statement import with that CSV file. However, I manually change the headers of the columns relevant to Manager to match that of the PayPal file I originally supplied to lubos.
I don’t know what you are talking about with regard to the screen shot of the file I’m trying to import because I did so on my previous post. The file originally had dozens of customer transactions, but I am not about to post that, so I left two in place and changed the customers names. If it would help, I could email it to support instead.
The second screenshot in this thread (above) shows a recent import that worked as expected. Additional screenshots are below. The first shows the result of an import in which the Fee is not being recognized now. The CSV file used is the same as in my earlier screenshot above.
Perhaps this would be simpler… Can someone tell me how, by using a CSV bank statement import like that shown in the last screenshot above, I can have the Gross Amount and Fees split as was done before (see my 2nd screenshot above in my posts since yesterday). I would be happy to change the column headings to something else if needed to make it work.
Well, this has been a lot of back and forth, complicated by the fact that there is a bug in the software, but it doesn’t affect what you are doing. The bug affects outgoing payments on which there are fees added, but not incoming receipts from which fees are deducted. I will address that in a separate bug report.
The bottom line is that I cannot reproduce your problem in version 17.7.41. Here is what I did:
- Created a .csv file in exactly the format you provided:
- Added your precise bank rules:
- Imported the .csv file. The import process told me I had 4 uncategorized transactions. Viewing them produced:
- Checked all the boxes and clicked Bulk Update. The sales figures were added to the proper income account. (Ignore the balance; my test company already had a balance in this account.)
- The fees show up properly in the Bank service charges account:
After extensive investigation, I can think of two possible explanations for your problem:
Since some of your examples did not line up (different transactions in import file vs. receipt example), there remains a possibility that cannot be explored from what you’ve provided. There is evidently logic in the program to verify the difference between gross and net amounts compared to the fee. (That is where the bug is.) So if you entered amounts that were not numerically consistent, that would have caused problems.
If you calculated the
Netcolumn using a formula, you get exactly the result you showed in post #14. The net value goes to the sales account, but the fees are ignored, because Manager does not break out the transaction into two lines for gross amount and fee. The .csv download from PayPal lists numbers, not formulas or their results. Put those numbers in directly, as I did, and all works perfectly (for receipts). This is what I really believe causes your problem.
Hold the presses. As I was writing the bug report I mentioned in my previous post, I tried to illustrate the problem and discovered I could no longer successfully import the same .csv file I used earlier. No updates to Manager; no changes to bank rules. I just deleted the previous transactions so the import would not be flagged as duplicates and repeated the same steps that were successful 2 hours ago–and successful several times.
I finally got things working by changing the column heading from
Gross amount to
Gross, which is how PayPal .csv files are downloaded. Having started with a PayPal download to recreate your example, I can’t positively swear I had the change to
Gross amount in place when I first got things to work, but I’m pretty sure I did.
Try your import with the heading changed to
Gross to see if that works.
Thanks again for your help. To answer your previous question, all spreadsheet columns, including “Net”, are provided by PayPal and Etsy in Values format.
You are correct in noting that “Gross amount” should be “Gross”. My mistake. After reviewing the bank rules and seeing the former, I changed that column heading from “Gross” to “Gross amount” to see if that would help. It didn’t, but I should have changed it back before making the screen shot. As an aside, the Fee value must always be a negative number, otherwise that transaction component won’t import, but that’s the way it always worked.
Fortunately, the software is installed in a folder backed up by Windows System Restore. I completed a system restore back to 7/21 and ManagerDesktop.exe, installed on 7/21, was replaced with the one from 7/8 (V 17.6.95). After that, the CSV import worked as expected. But if the current EXE works for you, then I need to do some backups, a fresh Manager install and more investigation of my local environment.
I could be wrong, but rather than rather than Bank Statement Imports it seems as though most users enter their transactions using Sales Invoices. If the latter is better in that it provides access to more features in Manager, I would be happy to use that method, but it would seem that manual data entry is required for every sale (requires more time and increases the possibility of data entry errors). However, that’s probably a topic for another thre
There are certainly many, many users who are absolutely devoted to bank imports, some reporting very high volumes.
That said, it is true that a sales invoice provides more flexibility and information. Specifically, it allows you to track the history with a customer, which receipts don’t do.
Personally, I would not recommend going backwards, because the program changes so fast you’ll soon be in the rear view mirror and no one will be able to hep with problems. (All the moderators use the latest or at least very recent versions.)
I also communicated on this topic with @lubos, who called the feature you’ve been taking advantage of “undocumented.” He said that he preferred no attention be devoted to understanding and documenting it better because he’s planning to change .csv imports to be more like batch operations. Personally, I don’t know enough to know why .csv would be any more difficult to handle than the other formats, but that’s why I’m not the developer.