Algorithmic Trading: Python vs SQL

Question

I am new to algo trading. But I have bit of coding experience in SQL. Now I am planning to develop a Algorithmic Trading system. In here I am storing all the historical data in Database (PostgreSQL DB).

Initially I was planning to code all the technical indicators and strategies in Python. But as I am using a Database now I am in confusion whether to write the code for technical indicators in Python or should I calculate those values in SQL only and store in the Database.

Please provide your thoughts, pros and cons and also any other suggestions.

Answer 1

Python has lots of excellent libraries to compute Technical indicators for you, ta and ta-lib are great. These libraries have dozens of indicators to use and their documentation is extremely detailed. Furthermore, these libraries are built on top of Pandas which helps in regards to pivot tables and db schema. The libraries are quick to run and very accurate as well.

This leads to my next point- it depends how much data you have and what you plan to do with. If you have large quantities of data, then storing in serialised arrays might not be the best and would hog your memory. SQL databases would be best for large quantities of data as you then use some clever API usage to CRUD to your database as well.

If I were you, I would compute the indicators using Python, either using the libraries or custom written functions. I would then store the data in an SQL database.

Answer 2

You said you're developing an algorithmic trading system. First, I'd suggest maybe consider an off-the-shelf product that will let you do some trading without starting from square one to save yourself time/hassle.

Now to the question at hand - use python. A SQL database's role is to store and serve relational data. The majority of the trading system is not this and you'll be better off to use a general purpose programming language. You can still use SQL on the backend and access it programatically with python through psycopg2 or sqlalchemy libraries. But for example, your trading system will need to do things like submit an order. You can't do this from SQL - this is done in the application layer and then the results can be persisted to the DB.

Answer 3

There are several considerations here:

1. Is your database server used solely for the purpose of this algorithmic trading? Or is it also used to support an application server?
2. How complex is your technical analysis going to be? Some TA are quite straightforward to implement in both SQL and Python, but I'd imagine implementing something like Parabolic SAR will be quite cumbersome in SQL. At least will be wildly inefficient because you probably need to use cursors.

With that in mind, I generally support putting as much as logic in your python side instead. Python has excellent libraries for vector-ized mathematics operations using pandas and scipy that makes implementing TA very natural. Use SQL strictly for storage and retrieval. This also has the advantage of if you decide to change your storage strategy (very possible, since for some data structure Time Series DB might be better, or you decide to store the data in flat filesystem), you will not need to change your logic layer. pandas have excellent support for loading data from various data storage as well.

Answer 4

You can use high performance provided by database servers for calculations with huge amount of data, however, SQL is a logical language aimed mainly on searching in database. It is not intended for highly complex calculations and modeling. However, for example Oracle database provides so-called analytical functions allowing you to do advanced dataprocessing.

I would recommend to calculate as much as possible in database to use up its high performance and to implement models (i.e. complex calculations needing advanced mathematical and statistical functions) in Python.