An Affordable Alternative to Excel for Traders

Excel is sometimes the strongest enemy other software can have. It's use is widespread, easy to learn and once you learn to use it provides a lot of freedom. There are alternatives to Microsoft Excel in order to create spreadsheets such as Google Sheets or OpenOffice which remove the licensing barrier.

All those benefits can later become a curse, once someone starts capturing data it won't necessarily be well structured, easy to query or process in order to extract something valuable out of that information.

I have been exploring alternatives and it's an ongoing project in order to limit the dependency I have to Excel. The process looks something like the diagram below which will be explained in detail later.

Information Flow

The image above, describes how the information would flow in terms of steps

1.- The Source: It can be actually anything, even a direct connection to some service such as any online dataset that you can afford or a source file such as a spreadsheet if you are on a budget. Assuming the latter, an option is to keep a spreadsheet or CSV file and back it up using something like Dropbox or any other cloud backup service.

Excel is easy to manipulate, if the information is stored in a structured way it can help as an interface to capture the information rather than developing a Windows UI or a Web interface. Just manually capture the data or copy & paste it and that's where the role of Excel ends.

If your process is already automated, using some batch program pulling the information from some online source then these steps can be skipped.

2.- ETL Tool: ETL stands for 'Extract, Transform, Load'. ETL tools are usually expensive and come with a steep learning curve, not to mention the infrastructure where they need to be installed. There are a few alternatives such as Talend Open Studio which can be used to get the functionality without having all those requirements.

The purpose of this tool is to take the spreadsheet and load the information into a destination such as a database. The way in which this is done is through jobs or mappings that are configured to do just that, below is a sample mapping that I created in order to load dummy data generated out of some old transactions I traded in the past.

3.- Database: Eventually the information needs to be stored in a place where it's easier to manipulate than a spreadsheet. One option is SQLite, pretty simple and efficient database that stores everything in a single file (another important file that can be uploaded to Dropbox or any other similar service). If you have more technical knowledge and infrastructure MySQL is another option, there are many DB engines out there with all kind of features for all type of budgets.

SQLite is open source so I like the price tag. Once the data is inside the DB it can be manipulated with SQL (Structured Query Language, the language of the databases such as the simple SELECT in the screenshot below). Having the data in a database means that you can structure the data in different tables, find the information required through SQL queries and add the power that a programming language can give you.

4.- PyCharm / Python: Python is a powerful programming language and one of the top tools used in trending technologies such as Machine Learning. Once the data is stored in the database is a matter of a few lines to connect and bring the data to the data analysis and manipulation libraries such as Pandas. PyCharm is a great tool, there is a free version called PyCharm Community Edition that has some limitations but it's pretty powerful.

I have been working in IT related projects for a long time and some of the people that become expert in complex tools such as Python they never studied Computer Science or anything related to it. It requires a lot of dedication to get to those levels, but if you are not interested in becoming a Python guru a few simple lines can do the trick.

There are other ways to exploit the information such as Tableau or Microsoft Power BI (at the moment of writing this article there is a free desktop version available). If you have the time to learn them and don't run into license issues with those tools they are also good alternatives to get insights into your data.

It is Worth It?

The answer that no one likes is 'it depends'. Would it give you an edge in your trading or not to have this kind of access to information datasets? Not everyone trades based on the amount of information they can gather, if you are like Warren Buffett then you don't even need a computer, if Excel has been enough and more importantly your equity curve is going up maybe this solution is not for you.

If you can afford a cutting edge infrastructure with all kind of fancy hardware and software, definitely this is not for you. However, if eventually you need to get started in something other than manually capturing trades out of a watchlist in Excel or your broker's platform, one or more of these tools can set you in the right track.

Links

There are plenty of options out there, here are only mentioned a few I have tried, the links at the time of writing this article are:

Conclusion

Big funds such as Renaissance Technologies spend millions of dollars in the infrastructure that allows them to trade and compete at the highest level there is in Wall Street. That infrastructure plus the brightest people they can hire is part of their edge when trading, think about what is your edge and if that edge will hold in the future.

I'm not saying a simple use of technologies such as the one described in this article is able to compete with the huge budgets those companies have access to. But as individual investors we need to strive to find ways to keep profiting from the Market despite the disadvantages we have compared to the big players. Whatever is your edge today don't assume it will work tomorrow and never ever get discouraged in this frustrating path the Markets set in a trader's life.