posted on January 18, 2005 07:46:26 PM new
Calculating Fees With Microsoft Excel
The easiest way to calculate the fees you have paid for the year would be to:
1. Download your transaction history for the year you wish to calculate and import it into Microsoft Excel.
2. Change the column format to Accounting by:
a. Click the Letter in Microsoft Excel above the Fee column. The entire column
will be highlighted
b. Right click on the highlighted area and select 'Format Cells'
c. Choose Accounting in the category window with 2 decimal places and click 'OK'
3. Click in the empty cell under the last amount in the Fee column and press the 'Auto Sum' button at the top of excel (looks like a sideways letter 'M'
The column will change, asking you to verify if you want a sum of the current highlighted column. Simply press the enter key on your keyboard and your sum of fees will be calculated and entered into the cell you clicked on in step 3.
Note: PayPal cannot help you with any issues regarding Microsoft Excel. You need to download your history as Comma delimited or Tab delimited to be able to import to Microsoft Excel.
posted on January 19, 2005 01:07:46 AM new
Thanks Ewora! That made it so quick and easy. Like most people, I put off learning something new until I absolutely have to know it. With your simple instructions, it made the Excel less threatening. Usually by the time I set something up in Excel, I've bumbled around and can't remember what I did to make it work.
posted on January 19, 2005 05:47:05 AM new
Your accounting at year end will be made so much easier if you do this by month. I print my eBay invoice and PayPal history by month. I attach a spreadsheet with my sales $ and my eBay fees. At the end of the year I only have to add them all up. Don't hate me, but I've got my taxes done already for last year. I used to put it off, too, and spend hours (even days) trying to figure it all out.
Cheryl
"No drug, not even alcohol, causes the fundamental ills of society. If we're looking for the source of our troubles, we shouldn't test people for drugs, we should test them for stupidity, ignorance, greed and love of power." ~ P.J. O'Rourke
posted on January 19, 2005 11:17:54 AM new
Cheryl, I used to keep all my accounting in Quicken. Could tell where every $ came and went.
Then I married a TN boy who believes in "The Flow". He thinks you need to spend it to get it and keeping track of it only slows down the flow. Drives me nuts!
With the new meds though I've lost the need for so much control, lol. Haven't updated my Quicken in months.
With Ewora's tip, it took me about 3 hours to clean up about 12 months of sales expense history. Not bad!
posted on January 19, 2005 04:04:02 PM new
Hi. You may want to note though that if you have received any payments via e-check, paypal makes two entries on your downloaded spreadsheet. 1-for the original payment, and one when it clears a few days later. If you simply do the @sum feature in excel, your balance will be doubled in the net and gross column for those entries. Because it will simply add up two payments of say 15.00 each in that column. But really there is only one payment of 15.00. What you can do is sort the spreadsheet by "ebay payment received" to easily see the double entry by buyers name, and then delete out the row with the double entry - but make sure you dont delete the one with the fee in it - and then re-sort it by date descending or ascending.
edited to add: Since you balance is not updated to reflect the money received until the payment clears, it can get confusing if you delete an entry - so what I do is delete the original entry and manually add the fee to the 2nd post - I think paypal enters their fee on the 1st one. Hope its not too confusing to anybody, if so, just leave the spreadsheet alone. The sort function is under >Data >Sort on top menu and you have to sort the Whole spreadspread.
[ edited by dblfugger9 on Jan 19, 2005 04:19 PM ]
posted on January 19, 2005 05:51:48 PM new
dblfugger9, I noticed that one last night. Also noticed some auction payments from buyers were listed as Ebay so had to manually clean that one up.
I break out Paypal fees, postage and ebays fees on my spreadsheet. That is how I noticed a sorting few glitches that affect the bottom line.
posted on January 19, 2005 07:17:00 PM new
Hi Itray. Yes it wouldn't matter if you were only trying to see your total paypal fees paid. But as you said, the bottom line is your sales less PP fees. I figured that out when I totaled all columns and my gross minus fees did not equal net balance and I saw the double entries being summed in gross and net. - And why they put the balance all the way over in column AK is beyond me!! I always insert a column next to K and cut it over there after the gross column. Feels better to see it in line - gross; fee; net; balance.
One other good thing I found out to to do is apply the filter vs. sort and copy the results onto another sheet. I do that for postage fees, or just to see all my all withdrawals summed into a total balance. Then I sometimes sort it out by month, or by w/d-place when checking receipts.
I think you were on the right track with quicken! It must be much easier to work with because quicken has functions pre-built in to do all that stuff. You just run the reports, right? I dont know if you can import directly from PP to quicken. I think you can with a CSV file. ? I remember doing that at a previous job. You know, with paypal I even found one entry where they charged me the fee on the 1st post and then on the 2nd as well. I was going to complain about it, but it was such a miniscule amount of change, and it was only the one time, so it seemed like a glitch or something they did manually. But I watch my account. The money does seem to come slow, and go out reaal fast
edited to fix my columns (mind is pretty much just on the net!)
[ edited by dblfugger9 on Jan 19, 2005 07:39 PM ]
