During the last few weeks at my previous job, I was working on a little project which was originally produced in Excel; so naturally I had to make it… better. I decided it was time to put what I’d been teaching myself to the test and built up this tool using Python (SQLAlchemy, Flask), JavaScript (JQuery, D3), and MySQL.

Using a predetermined universe of companies, the app allows you to compare company fundamental data based on certain accounting figures. Eventually, this is summarized into one number for each of the 5 categories (listed below) as well as an overall figure. A higher number is better, except for those values below with an *. Each category consists of multiple figures:

1. Short Term Liquidity

• Total Available Line of Credit
• Total Credit Lines Drawn
• Cash & Equivalents
• Free Cash Flow

2. Debt Ratios

• Total Debt/Trailing 12M EBITDA*
• Net Debt/EBITDA*
• Total Debt/EBIT*
• Net Debt/EBIT*
• EBITDA to Interest Expense
• EBITDA-CapEx/Interest Expense
• EBIT to Interest Expense
• EBITDA/Cash Interest Paid
• EBITDA-CapEx/Cash Interest Paid
• EBIT/Cash Interest Paid

3. Profitability

• Gross Profit Margin
• EBITDA Margin %

4. Debt Amount

• Short-Term Debt*
• Long Term Debt*

5. Size

• Market Cap
• EBITDA
• Revenue

All this data is stored in a SQL database. I’ve actually cycled through SQLite, PostgreSQL, and MySQL just to try out each one. For my company, I set up the database in PostgreSQL and wrote some Python scripts to easily populate the database from Bloomberg. I also used rules in PostgreSQL to ensure data integrity. When deploying on my new host, DreamHost, I switched over to MySQL, because that’s what they support.

The next piece is written in Python to expose the database via an API. I used SQLAlchemy and Flask to develop this part. I didn’t use any of the Flask extensions, though they looked promising, only because I wanted to understand the building blocks first. SQLAlchemy works like magic and connected to each database seamlessly allowing me to switch around easily. Using Flask, I was able to expose the API to fundamental.rajarshidas.com – check the page out for a quick guide.

The front end is built using JavaScript, particularly JQuery (for the DOM manipulation) and D3 (for the charting). I chose to do the bulk of the calculations in the front end, since it wasn’t particularly intensive. The code receives the data from the API. The numbers are scaled within the peer group between 0 and 1. This is then averaged out for each category. Each category score is averaged to give an overall score. The peer group result is just an average of the individual companies. Clearly, there’s a lot of slicing and dicing (and averaging) going on here and I would’ve loved to use R for this. To make my life easier, I created a dataframe object (I said I wanted to use R) and helper methods to be able to get slices of the data as needed.

To use the tool, first select a company of interest in the Security drop-down. This will refresh the static data below. Then, pick your filters. You can filter by year, country, sector, or even do a one-on-one comparison by picking another company. Generally, it makes sense to compare a security to a relevant peer group by looking at the statics that pop up. Obviously if you pick filters that result in an empty set, you won’t get anything useful back. There’s also a few securities in there that are missing data (such as private companies) so they may also return nothing. When you’re done hit Run and let it work its magic. The output consists of a table with the actual values of the company and peer group, a bar chart of the spread between the numbers, and a radar chart comparing the numbers directly. Press the red + button to reset. Play around with it and use it however you’d like!