TCA involves studying trade prices to determine if the trade was executed at a good price. Important metrics for carrying out TCA on a trade include the Volume Weighted Average Price (VWAP), mid-price, and slippage. The VWAP is the mean price an asset has traded at in a given time period (15 minutes before a trade for this test), weighted by volume. The mid-price is the mean of the bid and ask prices at the time of the trade. Slippage is the change in price between the decision and execution times – illustrated by the arrow in the diagram below which shows the share price of a fictitious company with an exaggerated difference between the decision and execution times for this particular trade. For more information, here is a KX whitepaper on TCA.
For this project, 3 million trades and 80 million quotes were taken from the NYSE TAQ data, spanning a single day (7th October 2019) and for symbols beginning with ‘S’ only. The file sizes for the TAQ data are about 12.7GB for the quotes and 3.6GB for the trades. It’s easy to see how the file sizes might scale for data spanning several days or weeks and for all symbols, not just those beginning with ‘S’.
Market data such as this is too large to fit in memory for most PCs. To solve this problem, an open-source Python library called Vaex can be used alongside FastAPI to build a simple API which can carry out TCA on market data of this size.
Introduction to Vaex
AquaQ is constantly working with large datasets where it is impractical or impossible to fit the data in memory, for example, the TAQ data above is larger than the 16GB of available RAM on the machine used for testing. With data of this size, it becomes very challenging to use traditional data science libraries such as Pandas.
An alternative is Vaex – which makes use of several concepts to be performance and memory efficient, keeping as much data on disk as possible. The first method to achieve this is virtual columns and lazy calculations. Virtual columns do not use extra memory as their values are derived from other columns. Lazy calculations are results that are only computed when the column values are required (for example when displaying results). Next is vectorised operations which refers to operating on a column as a whole instead of row by row (like in a for loop or the apply method). Another tool that improves memory efficiency is no memory copying. This is when tables resulting from filters are not stored as copies but as a reference to the original data, meaning no additional memory is used. Finally, Vaex uses memory mapping to pull the data needed from disk allowing users to operate on data larger than available memory. The Vaex website describes the above concepts in more detail.
Worthy of note is a comparison between the supported file formats of Pandas and Vaex. Like Pandas, Vaex supports reading from CSV files, however, Vaex also supports binary Apache Arrow files which is beneficial as binary files are quicker to scan.
Vaex is similar to Pandas both in methods and syntax, allowing users to almost seamlessly swap between the two libraries. With Python being the go-to language of many data scientists and developers, Vaex is an attractive alternative to Pandas when working with large datasets. The Vaex documentation gives a great introduction to the library with examples to get up to speed quickly. An important comparison to make is the apply method. This method has subtle differences in syntax between Pandas and Vaex but works in a similar way – applying a function row by row to a DataFrame. The apply method should only be used when necessary as it does not make use of vectorised operations which will hurt the performance of the code but is occasionally unavoidable – more on this later.
Reading TAQ Data
The Jupyter cell below shows how the TAQ data, in Apache Arrow format, is read into Vaex DataFrames. The main difference between displaying data in Pandas and Vaex is the lack of customisable index names in Vaex. Index names are omitted because its intended use is with very large datasets where having a named index column would be unnecessary. The mid-price is also calculated here, making use of Vaex’s virtual columns.
A subset of 100 random trades are selected and converted to a Pandas DataFrame. These trades represent the user’s trades that they want to analyse. 100 trades can fit in memory, making Pandas a suitable option here as these trades will be eventually accepted as JSON which can be converted into a Pandas DataFrame and back to JSON again easily.
The function used to find the VWAP and mid-price for each trade in the 100-trade subset is shown below. It accepts the Symbol and Time for a given trade and calculates the VWAP and mid-price by filtering the TAQ data. Passing this function into the apply method allows us to calculate the VWAP and mid-price for all 100 trades. Vaex does not have a merge_asof method like Pandas (an as-of merge joins on the closest value in the DataFrame), meaning a filter must be used to find the most recent quote for a particular trade.
Filtering cannot be vectorised (a filter cannot accept a full column at once), therefore each Symbol and Time pair must be passed in one by one to the filter for each trade, hence the use of apply. This hurts performance as an as-of merge would have removed the need for filtering which would negate the use of apply, both of which add time to the computations.
Another potential feature that may have saved time is a binary search to reduce the time taken to filter the DataFrames. A binary search works on ordered data by starting with the middle value and determining if the required result is greater than or less than this middle value (discarding the half of the list that does not fit the criteria) and repeating this process until it arrives at the value needed.
The timing for the TCA performance was carried out using Jupyter Notebook’s time magic – %%time. This calculates the time taken to run a cell and is a useful tool to get a rough performance benchmark. Passing the TCA function into the apply method takes roughly 600ms per trade on the machine used for testing, giving a total time of 1 minute for all 100 trades.
Building on the ease of use of Python and Vaex, using an API to carry out this TCA is a sensible next step. A Python library called FastAPI was used to build an API which accepts a JSON string of trades on which to perform TCA. FastAPI was easy to integrate into the code, requiring only an instance of FastAPI to be created and wrapping the bulk of the code in a function (with a decorator to define the root and type of request) to be called when a post request is made. The request returns the trades as JSON with the VWAP and mid-price as additional objects.
A minor adjustment required when passing in JSON is the format of the time data. This data must be passed as a string ISO timestamp – if not, Pandas will annoyingly convert it to a Unix timestamp. As this value is a string, the Pandas to_datetime method is needed to convert it to a datetime. Finally, Pandas read_json and to_json methods were used to convert the JSON to a Pandas DataFrame (on which the TCA was carried out) and back to JSON to be returned to the user. The source code for the project using FastAPI is available on GitHub.
FastAPI comes with a customisable Docs page accessed by adding /docs to the URL. The information on this page can be altered by passing in some arguments to the FastAPI instance and defining a multiline docs string for the post request.
Navigating to the post request on the docs page, the string defined in the code is included in the request description. From here, it is possible to make a function call from the browser by clicking ‘try it out’ and passing in the required parameters. Alternatively, the call can be made programmatically using the Python requests module and passing in the required JSON as shown below.
TCA using Vaex is a viable option as it allows users to make use of free software – Python and Vaex. This coupled with the widespread use of Python and the similarity of Vaex to Pandas makes Vaex a good alternative for post trade analysis where performance might not be the most important aspect to consider. Furthermore, while Vaex may still be too young to compete with the niche methods of other software, it is an excellent library to use on large datasets where memory would otherwise be an issue. Finally, it is quick and easy to integrate Vaex with FastAPI to build a simple API to perform TCA on sets of trades that require a large amount of market data.