Purpose of this Article
This article enumerates how the tax lot matching process can be modelled and the run in Couchbase. While the data model itself is a subjective thing, this article is going to only list the major entities involved in model and the N1QL query that can be used to run the Tax Lot matching process.
Before we dive into the solution I am assuming that the reader is aware or is willing to invest sometime getting familiar with some of the structures in N1QL which are very SQL like but has some extra JSON specific features.
- NEST clause can be used against arrays to transpose rows to columns
- UNNEST can be used to dis-assemble an array into rows
Since JSON documents and N1QL are so feature rich taking a quick primer on the syntax would be very useful
What is a Tax Lot?
A tax lot is a record of an opening transaction (e.g. purchase or short sale) in your portfolio. If an order is filled in multiple pieces, called partial fills, the prices will be averaged and will be represented by a single tax lot number. You can have multiple tax lots within a security's overall position.
Each time you purchase a security, the new position is a distinct and separate tax lot — even if you already owned shares of the same security. (A tax lot is a record of a transaction and its tax implications, including the purchase date and number of shares.)
A tax lot identification method is the way we determine which tax lots are to be sold when you have a position consisting of multiple purchases made on different dates at differing prices, and you enter a trade to sell only part of the position. We are required by law to track and maintain this information, and to report the cost basis and proceeds to you and the IRS.
Your choice of tax lot ID method can have a significant impact on the amount of taxes you may pay when you sell an asset.
Tax Lot Matching Methodologies
The queries that are being shown in this article will showcase only the FIFO method as it seems to be the most popular methodology but I am listing the methodologies all the same
First In, First Out (FIFO)
- The default method for matching tax lots. Sales are paired with the earliest purchases sequentially. FIFO assumes that assets remaining in inventory are matched to the most recently purchased or produced assets. FIFO is always used for futures and options on futures.
Last In, First Out (LIFO)
- Each sale is paired with the most recent possible purchase. LIFO assumes that an entity sells, uses or disposes of its newest inventory first.
Maximize Long-Term Gain – Sales are paired based on the following set of priorities:
1. Maximize Long-Term Gain per share
2. Maximize Short-Term Gain per share
3. Minimize Short-Term Loss per share
4. Minimize Long-Term Loss per share
Maximize Long-Term Loss – Sales are paired based on the following set of priorities:
1. Maximize Long-Term Loss per share
2. Maximize Short-Term Loss per share
3. Minimize Short-Term Gain per share
4. Minimize Long-Term Gain per share
Maximize Short-Term Gain – Sales are paired based on the following set of priorities:
1. Maximize Short-Term Gain per share
2. Maximize Long-Term Gain per share
3. Minimize Long-Term Loss per share
4. Minimize Short-Term Loss per share
Maximize Short-Term Loss – Sales are paired based on the following set of priorities:
1. Maximize Short-Term Loss per share
2. Maximize Long-Term Loss per share
3. Minimize Long-Term Gain per share
4. Minimize Short-Term Gain per share
Highest Cost – Seeks to maximize losses while minimizing gains. Sales are paired based
on the following rules:
- First, it looks at all possible options for matching a closing trade to an open lot.
- If any possible matches would result in a loss, the method chooses the match that
results in the largest possible loss.
- If no possible match would result in a loss, the method chooses the match that results
in the smallest possible gain.
Specific Lot – Lets you see all of your tax lots and closing trades, then manually match lots
to trades. Specific Lot is available for today or a position, but not it is available as the
Account Default Match Method.
Using a live Example
Given the inputs look like this
The business rules state that In order to minimize taxes , each transaction needs to be paired with a transaction occurring after the current transaction with an opposite TradeType eg : B signifies Buy and it will need to be closed out with a transaction having the opposite TradeType in this case S or SELL and the Quantity needs to be equal to or greater than the Trade that is being closed out and has to have a TradeDate greater than the TradeDate of the trade being closed out.
Entities in a Specific to Tax Lot Matching
The assumption is that this is being run by a hedge fund, financial services firm that services several clients, customers. While the # of elements that are captured are much more comprehensive than what is listed here, my attempt is to merely showcase the most important elements that participate in the Tax Lot Matching process. Modelling this in Couchbase can take two paths
- If you are migrating from a relational database and would like to quickly migrate some data over and then adapt the NoSQL so it models your business, you could create a document type for each entity i.e. Trader could be one entity, Instrument could be another entity. And you can include a type element in every document that would signify the document type
Sample Relational Model would look like this,
Translating this verbatim to adapt to a document model the entities would look like this
Sample de-normalized model for the TradeOrder Entity which is really where the TaxLot Matching process will happen
Running the Tax Lot Process using N1QL
We are going to assume the FIFO method for TaxLot Matching. While this process is very well suited for a functional language because it needs to iterate thru the rows thru a pre-determined order and needs to cache the result set, I wanted to enumerate how you can run this in N1QL because of the richness and versatility of the language.
- Assumption is that the reader is familiar with Couchbase and can navigate thru the admin console
- Create a Bucket, let us call it Taxlot_Demo
- Create the required documents so you have a good testbed
- We will then create a temporary bucket that creates an array of all trades for a group of match criteria. Assuming in this case that we want to only match between a given fund, trader, broker, strategy, instrument and TradeType the before and after picture looks like this.
5.Then in a single query we will obtain the tax lot matches, since the query achieves a lot I will break it down into steps for ease of understanding and list the full query at the end. Step e is the only query required to run the Taxlot process on Couchbase, the intermediate bullets a,b, c, d only enumerate the bigger query on step e.
a. The inner most query in step f creates a concatenated column that can be matched with the key from step 4.
b. Next I join it to the materialized table created in step 4, the result of this query will be each trade within a grouping criteria and the array of possible matches.
c. Then I UNNEST the array to create a many to many relationship between all rows within the group FundId,TraderId,BrokerId,Strategy,InstrumentId,LongShort
d. Then I apply the business rules to get an opposite signed transaction to find a tax lot match
e. And then finally pass back only the records that need to be closed out.
So given your dataset looks like this, Trade 1 closes out with Trade 2 since trade 1 is a BUY transaction and it matches with Trade 1 which is a SELL transaction. Trade 3 does not have a match hence will not close out.
Likewise Trade 4 closes out with Trade 5.
Trades 7 thru 9 are all BUY transactions and do not have a matching SELL transaction and hence do not close out
The purpose of this article was to enumerate how N1QL can be used to solve very complex data access queries. If one is used to writing complex functionality in SQL it can now very easily be done in N1QL. The Examples used for this article have purposely been simplified so that it is easy to follow , it is possible that a couple of tweaks or extra joins or queries may have to be added to arrive at the result your organization is looking to get.
For a better understanding of Couchbase Server architecture refer to the following link http://www.couchbase.com/NoSQL-databases/couchbase-server
For a deeper exposure to N1Ql and the constructs it offers , check this link
This article has been written by Sandhya Krishnamurthy, Senior Solutions Engineer at Couchbase, a leading provider of NoSQL databases.
Contact the author at firstname.lastname@example.org
Visit the sites below to learn more about Couchbase products, for free product downloads and free training