Some basic accounting with Paypal and OpenOffice.org

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

One of the great things about Paypal, other than it being a great service for accepting & sending payments online, is that it keeps a history of your transactions and makes this history available for download in formats you can use with popular spreadsheet software.

This morning I sat down and played with a TSV file of my history I downloaded from Paypal in OpenOffice.orgs' spreadsheet application.
OOo came with my installation of Ubuntu under the Office menu, and after giving Mozilla Sunbird a try for a day planner yesterday, I figured I might as well give OOo a whirl.

To get my TSV I logged in to Paypal and clicked the "Download History" menu item that shows up under the "History" link that's under the tab bar.

Attachments:
history.png


On the resulting page I selected a date range to be downloaded, and selected "Tab Delimited - All activity" from the types drop-down.

Attachments:
download-history.png


Then I clicked the "Download History" button down and to the right of there to download the TSV file.

Once I had the txt file downloaded I opened up the OpenOffice Spredsheet application and loaded the file I downloaded from the File menu.
I left everything as-is in the dialog box the application gave me about how to deal with the file and clicked "Ok".

The spredsheet once opened looked like it does in any normal spredsheet application I guess, just a bunch of rows and columns, probably more than 50 columns, with the first row including the column titles.

The first thing I did was right click the row number of the first row and copied that row, then I right-clicked a number of a row a couple of rows below the last row in my spredsheet and selected "paste". that way I had the titles above the totals I wanted to produce.

Attachments:
copy-title-row.png


The first thing I wanted to find out was the sum of all payments I'd received in the timeframe.
So I double-clicked a cell in the "Type" column a few rows under the header row I pasted and typed in "Payments Received".

Attachments:
payments-received.png


Now the next part took me awhile to figure out because I had limited experience with spredsheet applications, and it hadn't donned on me that I needed to filter out certain rows for some things, but after getting the hang of it the rest has been a breeze.

I clicked the "Gross" column two cells right of the new "Payments Received" cell I added to focus it, not double-click, single-click.
With that cell selected I clicked the "fn(x)" button towards the top of the application to bring up the function wizard.

Attachments:
fn-button.png


From the function wizard I selected the "SUMIF" function from the list and clicked the "next" button.

Attachments:
function-wizard.png


At first the three pieces of information the function wanted completely baffled me & the descriptions didn't help me much either. But after multiple trips to the Help button I managed to figure out a few things.

The first thing was that "Range" expected a colon-separated range of cells to apply the condition to.
Since for my "Payments Received" column I wanted to sum up all of the numbers for received payments, I applied it to the "Type" or "E" column. The range started with e2 which was the first row after my title row, and ended with the last row in my spredsheet that had information.

The "criteria" part just wanted to know what the value cells of each row in the "Type/E" column should contain in order to be included in the summing up of the numbers. For this case I just wanted payments so I entered "Payment Received" into the box.

Lastly, I had to apply the same idea used from the "Range" box to the "sum_range" box to tell the wizard which rows and columns should be summed up if the criteria for the range cells was matched.

Attachments:
function-wizard-done.png


Then I clicked the "Ok" button and was presented with a nice total next to my new "Payments Received" cell.

Attachments:
payments-sum.png


Now sometimes people just aren't happy and I have no choice but to issue a refund, so I repeated the same process as for the "Payments Received" row, but using "Refunds Issued" for the row title and "Refund" as the criteria in the function wizard.

Attachments:
refunds-issued.png


Now knowing both of those numbers I added another row this time with the title "Payments Minus Refunds", and instead of using the function wizard this time entered some simple math into the bar by the fn(x) button and clicked the checkmark to apply it to my column.

Attachments:
minus-refunds.png


Attachments:
minus-refunds-total.png


After that I repeated the process used for the refunds and payments to sum up a few other numbers, like ATM withdrawals/fees since I have a Paypal Debit card, and how much had been moved into the bank. I also realized that there were "Web accept payment received" rows that weren't getting counted into the totals, so I had to change the criteria used into a regular expression to match both of them. (I actually forgot about subscriptions too, but don't want to update the screenshot again)

Attachments:
regex-payment-received.png


It's quite handy being able to use regular expressions within the criteria when summing up numbers, that's for sure.
It's also nice to know that changing one cells math, automaticly changes the results in other cells depending on them.
One more thing that's nice to know is you can copy and paste the line next to the checkbox in those screenshots from one cell into another and just change the criteria before clicking the check.

That's about it for what I learned, other than one more example for determining how much I wasted on Fast Food. :D

Attachments:
fast-food-fn.png
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

Post Information

  • Total Posts in this topic: 1 post
  • Users browsing this forum: No registered users and 42 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.