by 4 comments on "Tax tricks: Join multiple CSV files, remove duplicate lines, and a spreadsheet trick to very quickly sort business transactions by keywords you choose."

Tax tricks: Join multiple CSV files, remove duplicate lines, and a spreadsheet trick to very quickly sort business transactions by keywords you choose.

Trick for sorting your business expenses using a spreadsheet, quickly

Here is a quick summary of what will be covered in this article, useful for people that own their own small business but do not want to buy specialized financial software:  downloading CSV files of your transactions for bank accounts and credit cards, and how to batch concatenate (join) multiples of them together all at once, how to remove duplicate lines, and finally how using a spreadsheet you can quickly use keywords of your own to sort all of your expenses into categories like meals, entertainment, hotel and travel expenses.  This is not only useful for small businesses but good if you like to keep track of your personal expenses as well.  

Most banks these days allow you to download your transaction history.  If you’re like me, I use a credit card for most of my transactions and get a cash back reward.  99% of my transactions are by credit card, and some transactions by debit.  I download the .CSV files every few months and when tax time comes around I use these CSV files for the primary sorting of my business expenses.  Instead of using paper receipts, I use my memory and the transaction descriptions in the credit card or debit CSV files I downloaded.  Some banks only maintain 3 months of transactions for you to download, some banks 6, 9, 12, 18 or unlimited months of transactions.  Be sure to have a reminder set up so you don’t forget to download the CSV files!  This will save you a lot of time come tax time so you won’t need to be going through your paper statements and typing out numbers manually.

How to batch join multiple CSV files using a Windows PC

It’s quite simple really.  Put all of your CSV files for your credit card into a single folder.  CSV files are very simply comma or tab delimited text files with line breaks after each row.  You just need to make it into a big text file first and let the computer do this for you to save time.  If you’re familiar with DOS batch files, simply create a batch file in that folder with the following command:

copy *.csv all.csv

This will take all .csv files in the folder and make a single file called “all.csv” that you can open with a spreadsheet application such as Excel or OpenOffice.org  (which is free and I highly recommend as a Microsoft Office alternative).  If you do not know how DOS batch files work, just download my batch file here and unzip it into the folder with all your CSV files.  Double click on the batch file (.bat file) and it will run on Windows 7 for example, or any other copy of Windows.

How to remove duplicate lines from a large CSV file for free with TextPad

For Windows there is a program I use called TextPad.  It is a fantastic simple text editor.  Open your concatenated large CSV file with TextPad and from the Tools menu choose “Sort”.  In the Sort dialog, at the bottom there is a checkbox called “Delete duplicate lines”.  Be sure to check this and all the duplicate lines will be deleted and a bonus you are sorting the CSV file too.  Save the file and open the saved file now with your spreadsheet application as a comma or tab delimited CSV file.

How to use a spreadsheet to quickly sort your expenses into categories of your choosing

I have created a spreadsheet template for you already and you can download it here.  Below I provide you with graphical instructions on how to use the spreadsheet.  Click the image for a larger version.

I have included a button in the spreadsheet that is presently configured to launch a Macro function that will look for keywords you choose out of column “C” and delete any rows containing those keywords.  For example, this is useful to delete rows that you do not want in the spreadsheet such as rows containing transactions of you paying off a credit card, or other transactions like email money transfers, or transactions between accounts.  If using Excel, you will need to adjust your security settings so that it will enable the macro to run.

You can expand the spreadsheet to contain any number of transactions, simply select a bunch of rows by highlighting a bunch of rows using the row numbers along the left and then right-click on the row numbers you selected and select from the popup menu “Insert rows”.  The more rows you select the more it will insert, you can repeat this a few times until you have thousands of rows.

Be sure to “Fill down” and “Fill across” so that the sorting equations in the main body of the spreadsheet are there so that you don’t miss any tabulations.  Each cell has a formula and the logic of each formula is this:  if the keyword you typed in the left column matches the column heading keyword, then this cell is equal to the transaction amount going out.  If this cell is not equal, leave cell blank.

I have included a column on the far right hand side with conditional formatting in orange that warns you when a row does not contain a sorted value, you can use this to see when you’ve made a typo on the keywording for example.  Sum totals are at the bottom of the spreadsheet.

I use a sheet similar to this every year for my taxes and I am able to sort about 1000 transactions per year in this way, in about 1 hour.  This is a huge time savings because sorting 1000 transactions with paper would take perhaps days of my free time.

Happy taxes!

 

 

 

4 Comments on "Tax tricks: Join multiple CSV files, remove duplicate lines, and a spreadsheet trick to very quickly sort business transactions by keywords you choose."


  1. You have some Duplicate Files on your computer which hampers your memory space.
    Just use “DuplicateFilesDeleter”.If you use this software you will be get comfortable.


  2. Duplicate file are really disturbing. Just use “DuplicateFilesDeleter program”


  3. Sometimes we get an error when we try to delete a File or a folder for no reason , but of course there is a reason.We have many damage file or blocked files.Do not worry if we want to remove the error files or too long path files from our system,here I suggest a smooth way.So use “Long path tool” software and keep yourself.


  4. Sometimes we get an error when we try to delete a File or a folder for no reason , but of course there is a reason.We have many damage file or blocked files.Do not worry if we want to remove the error files or too long path files from our system,here I suggest a smooth way.So use “Long path tool” software and keep yourself

Comments are closed.