Each organization has their own “standard” file layout and unique requirements for both the file and how the information is to be transmitted.  Add to that symbology and decimal point requirements and the situation can quickly get out of hand.

TheBooks has a robust trade reporting facility which allows the end user to define file layouts and data transmission methods allowing the complete automation of this important operational function.

Here is a quick tutorial to get you started.

The Problem

You have just been given an allocation from a large fund of funds and they have specified that you must provide them a trade file at the end of each day that includes all trades done in their account.  The file is to be sent via sFTP, use CQG symbology, and indicate trade changes from prior days by including correction, cancellation, and new indicators within the file.

The Solution

To set this up in TheBooks, you must do three things:

1. Define a contact that represents the Fund of Funds and specify the sFTP information to use when sending files to the organization
2. Define the file layout for the file to be sent
3. Associate the account(s) in TheBooks to the contact specifying the file layout defined in step two and indicating the frequency the data is to be sent.

Define the contact

Contacts are defined using the Configuration -> Counter Parties option.  From there, you add a new contact, then using the FTP tab on the contact property sheet, define the sFTP information as shown below:

Configure the file

File layouts are also configured using the Configuration -> Counter Party option.  Clicking on the Outbound Data Formats folder on the left portion of the window displays a list of available formats.  We will be defining a new format, but to make things easy, we’ll start with an existing one as a model.

Selecting the item called “Default Activity Summary Format” and clicking on the Clone button on the toolbar causes TheBooks to create a new file layout using the selected layout as a model:

Given the requirements for the file, a few changes to the default settings are required:

• Both the Action and AllocationRowID columns should be added to the file layout.  This is done by selecting the columns in the left hand list of columns and clicking the include button.
• CQG should be selected in the Translate Market Symbols using this source combo box.
• The TranslatedMarketSymbol column should be added to the file because this is the name of the column that contains the CQG version of your market symbol.

The Action column causes TheBooks to generate CANCEL/CORRECT/NEW items in the file enabling trade changes to be reflected in the file and the AllocationRowID column provides a unique number that the receiver of the file can use to associate changes to the trades.

Once this has been done, give the file layout a name and the click OK to create it.

Associate the account

The last step in the process is to link the account with the contact.  This is also accomplished using the Configuration -> Counter Party option.  Click on the folder called Contacts that receive trading activity and click on the New button.

Select the contact and account from the dropdowns, select the Notify options to Daily, send a CSV via FTP in the new format you just defined and click OK.

That’s all there is to it.  Now, at end of day, the client will automatically receive a data file of trades sent via sFTP containing activity for their account.  Any changes to prior-reported trades will be noted within the file and the market symbols will be CQG symbols rather than the symbology you use.

Summary

Providing customized trade files to investors, clearing brokers, 3rd party administrators, and others can be a daunting task for a CTA with managed accounts.  For users of TheBooks, providing these types of files, customized to each recipient, is accomplished using a few, simple, configuration tasks.

In this topic, I will show you how to use Excel to access data from TheBooks without any programming at all.

Background

Microsoft Excel has the ability to retrieve data directly from SQL Server databases without the need for any programming. TheBooks uses SQL Server as its database and maintains a number of what are known as Views which contain summarized data for use by other applications. Examples of the types of data available from these views are 13-column performance data and performance attribution by instrument.

The process you follow to retrieve data from TheBooks’ database into Excel is:

1. Define a Data Source which tells Excel where the data is located
2. Define a Query which tells Excel what data to retrieve
3. Define what to do with the data once you have retrieved it

The example shown below uses Excel from Office 2007.

Define a Data Source

A data source provides a link to TheBooks database. Once you have defined a data source, you can use it for multiple queries.

First, select the Data tab and select From Microsoft Query from the From Other Sources option.

If this is the first time you are defining a query, you will need to create the data source. In the future, you can skip the next few steps and select the data source you previously created. Since this is the first time, we will choose to create a new data source.

By clicking on OK, the SQL Server logon dialog is displayed. Clicking on the Options> button will expand the window and show the dialog below. In this example, the server is called g2\sql2005_x32 and TheBooks database is called Demo. Your server name will most likely be different and your database will probably be called TheBooks. I have selected to use Trusted Connections which means that my windows logon will be used to access the database. You may have to specify a username and password if your IT organization requires. See them for details.

Clicking OK will save the definition and open the dialog shown below. You should give the data source an easily understandable name so you can reference it later. You should select the SQL Server Native Client 10.0 driver from the list of database drivers. Finally, select the default table for the data source. In our example, I am choosing the view that returns trading returns by instrument, converted to USD. For detailed descriptions of available view, see TheBooks documentation.

Now that the data source has been created, you can select it for use in the query to return data. Remember, if you have already created a data source, you do not have to do it again; skip the previous steps and simply select the data source you previously created to begin the definition of the query.

Define your Query

You start building your query by indicating the types of database objects you want to work with. You should always select only Views since TheBooks pre-defines these for the most common types of data you would want to report on.

Clicking on OK brings up the Query Wizard. Scroll the list of available tables and columns to select the view you want to work with. Then, add the columns you want to return to Excel.

Once you have selected the columns you want to include, click on Next>. This brings you to the filter options. These options allow you to filter the data that is returned. In this example, I am restricting the data returned to only that for 2009.

Clicking on Next> brings up the sort order options. This controls how the data is returned to Excel. You can select as many columns as you like and specify the sort order for each.

Clicking Next> brings you to the Save Query dialog. This allows you to save the query you just build for use later. This is useful if you want to retrieve the same data and use it for another report or chart.

Working with the Data

Now that you have defined the query, you now must decide what to have Excel do with the data that is returned. In our example, I am going create a pivot table and also include a chart of the data.

Clicking on OK causes Excel to retrieve the data and insert it into your spreadsheet. If you are accessing a large amount of data, this may take some time. Excel provides an indication of its progress in the lower right of the window by showing how many records have been processed.

Since I selected a pivot table, Excel displays the pivot table designer once the data has been retrieved.

To add columns to the pivot table, simply select them from the list on the right. Excel does a good job of deciding if the column should be a value or a row label but you can drag them to where you want them for your report in the lower right section of the screen.

The data in the table is a daily detail of instrument returns by sector. If I want to have it summarized by month so I can easily graph it, I need to have Excel group the trade date by month. This is done by selecting the Group option from the Option tab.

This gives me the ability to group by month and expand or contract the data as I see fit.

Because I also asked Excel to include a chart, whatever I have included in the pivot table is automatically incorporated into a chart. The data in the chart is displayed at the same grouping and summary level as that displayed in the pivot table. I have selected stacked bars for my chart style so I can show monthly income and show what comprises each month’s income.

Summary

I have shown how easy it is to produce custom reports and charts from within Excel by retrieving the summary data produced by TheBooks. This data can be brought into Excel and manipulated without any programming experience by using capabilities provided by Excel and TheBooks.

Many advisors find the development of these documents tedious at best and the thought of producing them in the first week of the month unthinkable.

For users of TheBooks, the process is fully automated since TheBooks includes a monthly summary template which automates the production of this critical piece of marketing documentation.  Because the data comes directly from TheBooks, the document can be produced as early in the month as desired.

The author of the tear sheet works in the familiar Microsoft Word environment and has a wide range of document parts to work with. Since the tool is Word, document layout, fonts, colors, and chart appearance are all under the control of the author. TheBooks simply fills in the data as required.

The template provides the following document parts for the author to include in the document:

• Sector Returns Chart
• Program Statistics Table
• Monthly Performance Table
• VAMI/Assets Chart
• Returns Distribution Chart
• Benchmark Comparison Chart
• Monthly Performance Comparison Chart

Where do the benchmarks come from?

TheBooks includes the eight industry-wide Barclay indices and allows you to include user-defined benchmarks. User-defined benchmarks are defined within TheBooks using the Data Manager. Once defined, TheBooks automatically obtains the data and makes it available for reporting and comparison purposes.

Summary

As a CTA, you must create a program tear sheet on a monthly basis.  For users of TheBooks, the process of creating this important piece of marketing material is greatly simplified through the use of provided templates, the immediate access to performance data, and the availability of standard and CTA-specific benchmarks.

Background

The Clearing Corporation developed the APS system in June of 1992 as a way of making the execution process more efficient. Then in 1994, they introduced APS “Plus Five,” creating a consistent, industry-wide standard for calculating and processing average price trades for clearing and bookkeeping systems.

This method involves rounding the weighted average fill price to the nearest tradable price (up on a buy and down on a sell.)  Each account is filled at that rounded price and then given an “APS Adjustment” in the form of a cash credit to make up for the slightly worse than average price each account receives.

This has been the norm for APSed orders until late 2009 when some brokers started clearing the trades at the numerical average price and dispensing with the APS adjustment. This provides the clearing firm one primary benefit, no need to provide cash adjustments.

The issue for Traders

The result of this change is a situation that rivals the complexity the APS system was designed to solve.

Where once every account got filled at the same price, now accounts get filled based on where they clear.  If a block trade includes accounts that clear at brokers that pick up the trade at the rounded (APS) price and accounts that clear at brokers that pick up the trade at the numerical average price, accounts get different fill prices even though the trade was APSed.

When reporting give-up’s and trade recaps, this difference based on where an account clears must be taken into account. Reconciliation is equally impacted since these changes affect trade confirmations, positions, and cash balances.  OTE and realized gains for two accounts that trade identically are now a function of where they clear.

How TheBooks can Help

TheBooks includes the ability to indicate that a clearing broker either does or does not pick up APS trades at the rounded price with APS adjustments. When block trades are APSed, those accounts that clear at FCMs that pick up the rounded price get filled at the rounded price and are given a cash adjustments, those accounts that clear at FCMs that pick up the trade at the numerical average get filled at that price without cash adjustments.

Counter-party notifications, allocation files, and other trade-related notifications automatically incorporate the appropriate price formats. Reconciliation understands these changes and the precision used by each broker for numerical average prices can be configured to work around any variances that exist in FCM back office systems.

Of course, since TheBooks incorporates multiple price allocation methods for fairly and consistently allocating multiple fill prices accross the accounts that make up a block trade, perhaps the easiest approach is to dispense with the APSing of trades altogether.

FIX stands for Financial Information eXchange (FIX) Protocol and is a messaging standard developed specifically for the real-time electronic exchange of securities transactions and is supported by most trading partners. TheBooks supports both versions 4.2 or 4.4 of the protocol and several vendor-specific variations of these versions.

Let’s examine one application of this capability and how it can streamline the operation of a CTA or hedge fund with managed accounts:

Background
Our hypothetical advisor has both discretionary and systematic trading components in their programs and have a fund as well as several managed accounts. They execute trades through multiple platforms through multiple brokers and have clearing relationships with three separate firms. They trade most or the major liquid global futures markets as well as some options and equities.

Each trading partner requires trade allocation information to be transmitted to them via an sFTP file transfer in their own specific format as soon as the trade has been filed. Each of the claring firms require an end of day file with trades they should expect from the various executing parties, and several 3rd party administrators as well as investors also require a daily recap of trades done in their accounts either sent via email or sFTP.

System Configuration
The diagram below provides a high-level view of how the advisor is configured to communicate with the firm’s various trading partners:

The FIX interfaces are implemented as point-to-point, always up connections over VPNs. The sFTP and email connections use the internet and are used as needed.

Trading Operations
Discretionary traders trade through multiple platforms such as TT’s X_TRADER and Goldman Sac’s Redi+. System-generated trades are placed into the market through the programming interface of a popular trading platform.

As trading occurs, fills, regardless of source, are automatically routed to TheBooks via the FIX connections. Fills for each ticket are consolidated into trades within TheBooks and are booked to what is known as a holding account.

This is done because as orders are worked, the ticket number for a trade typically changes as the limit is moved. This is a function of the execution platform, but the behavior is pretty standard across platforms. The result is that multiple tickets could really be part of a single trade. By having the trades booked to a holding account, the advisor can select multiple tickets that are to allocated to a block of accounts as a single trade.

For platforms that do not generate new ticket numbers as the trade is worked, TheBooks could be configured to automatically assign the trades to a pre-selected block of accounts.

Post Trade Allocation
After a logical trade has been completed, the trader or back office personnel select the ticket(s) which make up the logical trade and associate them with the group of accounts which are to be allocated to the trade. As an option, the trade can be associated with one or more strategy (or system) to allow tracking of virtual positions and performance by strategy.

Contract quantities are automatically assigned based on the each account’s relative trading size and once saved, quantities and fill prices are allocated to the accounts.

If the advisor wanted to work up trades in advance of execution (using TheBooks ability to combine trades from multiple systems/strategies, for example), the assignment of the ticket to the block of accounts can also be done by simply selecting an existing trade rather than specifying a group of accounts. In that case, the fills from the tickets are “pushed” into the existing trade.

Counter-Party Notification
Once the block order has been allocated, TheBooks automatically sends allocation information to those 3rd parties configured to receive it. In the case of the executing broker, this is typically done immediately. In the case of clearing brokers, administrators, and investors, this data is typically sent at the end of the day.

In all cases, the advisor is able to configure the format of the file, including symbology to be used and the transmission method (email, FTP, sFTP, etc) and whether the information is to be sent at trade completion, at end of day, or both.

Summary
By using TheBooks FIX drop copy interfaces in conjunction with its order management capabilities, this advisor easily implemented a diverse portfolio of trading strategies and execution platforms across multiple managed accounts with a minimum of manual effort.

What makes things even more frustrating is that most of the effort is spent reviewing trades that are correct in order to locate the ones that are wrong.

TheBooks reconciler automates this process and presents you with the trades, positions, and balances that do not agree, eliminating the tedious process of manually locating discrepancies.

Statements, not data files

TheBooks reconciler reads human-readable statements, not data files. This allows you and the software to use the same source when reviewing the information provided by your brokers.

In addition, TheBooks automatically organizes, encrypts, and compresses your statements and saves them within its database providing a single point for backup. It also includes facilities for exporting your statements onto disk in an organized fashion allowing you easily prepare for audits.

A three step approach to data extraction

TheBooks reconciler goes through three phases in preparing the data on a broker’s statement for use in the reconciliation process. These steps are outlined below:

The third step, validation, is what converts the data from the various standards that exist on broker statements into the standard you have defined in TheBooks. This step uses what are known as statement symbols to translate and, if required, re-value symbols and prices and is based on the settings you configure. In this way, TheBooks performs standards-independent data transactions from the broker statements into the format you desire.

Only the issues

When you ask the reconciler to look for differences, it only shows you the trades (or positions, or balances) that are different. You then spend your time resolving the issues; rather than looking for the issues. In addition, TheBooks reconciler maintains a list of open trade breaks and includes them on any break sheet you send to your broker.

As missed or extra trades are corrected on your statements, they are automatically removed from the list of open breaks. Price corrections are also detected and reported.

Summary

TheBooks reconciler’s ability to read the same statements you read, convert the symbology and price conventions to your standard, and to present you with only the issues, makes it an invaluable tool for the CTA with managed accounts.

For more information, see TheBooks Reconciliation at www.dmaxx.com

On the surface, it is a great idea; unfortunately, it is not likely to succeed in the context of a CTA or hedge fund that trades futures and/or FX; not to mention over the counter instruments such as swaps.

There are several reasons why this can’t work reliably. The most notable are:

• Traders of any size have out trades every day. Basing accounting on the contents of broker statements that have missed or incorrectly booked trades is guaranteed to be wrong.
• Broker statements for futures are cash based, not accrual based. They do not include the accrued trading commissions for the exit side of the trades. If performance is based on the contents of the statement, that performance will be over-stated by the amount of the accrued exit commissions. The statements also do not contain accrued interest on cash held at the broker. Again, basing performance on the contents of the statements results in inaccurate performance calculations.
• Statements do not include management or incentive fee accruals. These must be calculated outside the context of the data on a statement.

Another problem that believing what is on a statement causes is it defeats the whole concept of reconciliation. By accepting what is on a statement, the advisor is in effect, doing the same thing as never balancing a checkbook.

In the case of an investment manager, he is being paid to manage the money of his investors. It is the manager’s responsibility to ensure that trades are booked in the correct account at the correct price, that positions are accurate and balances are in line. Basing accounting on the contents of the statements bypasses these important checks.

A better approach
If you assume an investment manager must maintain the list of trades she has made as well as her open positions in order to effectively run her business, she is already most of the way to being able to produce the required financial performance records.

A system like TheBooks is designed to take advantage of this fact. It provides simplified recording and reporting of trades and automatically keeps track of positions (both net to the street as well as by system/strategy) and it generates the appropriate accounting entries resulting from those trades and open positions.

A clear advantage of this is that reconciliation can be performed at the trade, position, and cash balance levels (another capability of TheBooks). In addition, because commission, fee, and interest accruals are also performed by TheBooks, the resulting accounting and performance reporting numbers are accurate.

Another advantage to performing your own accounting rather than basing it on the contents of broker statements is time. If accounting/reporting is based on broker statements, there is no easy way to provide end of day performance reports. All reporting must wait until the statements are received; typically early the next day.

TheBooks reporting is based on the trades that have been posted and the market prices as they are now. This means that end of day reports detailing daily/monthly/annual performance (summarized or detailed by sector and/or market) can be sent automatically at the end of the trading day; significantly improving the level or reporting and transparency an advisor can provide to her clients.

Summary
An advisor must maintain a record of his trading activity. Using a system like TheBooks not only maintains a record of all trades, it maintains positions and generates all the performance and accounting records required in a way that delivers more accurate and timely reports than those that can be produced by using broker statements.

Beyond that, it provides the basis for robust trade, position, and cash balance reconciliation, helping the advisor manage the inevitable trade and position discrepancies that occur when trading futures and FX.

The 2.2.1 release of TheBooks adds the following features to the Data Manager:

• The ability to define derived data sources. These are data streams which are based on other data streams (more about these later…)
• The ability to overlay your actual trading history onto price charts. The running position and P&L can also be charted as part of this option. When trades are overlaid onto a continuous contract chart, the trade prices are adjusted to match how the continuous contract was adjusted.
• Direct access to CSI data via their API. You no longer have to export data from Unfair Advantage to incorporate the data into TheBooks. You just tell TheBooks what computer in your network is running CSI and TheBooks is able to directly access the data.
• The charting package has been completely re-written and is now a high-performance utility with the ability to quickly scroll through large data sets and zoom in and out allowing you see as much or a little detail as required. In addition, gaps in the data can be highlighted as can roll dates.
• We have added the ability to easily switch between viewing any data set by simply clicking on the data set name within the viewer.
• Export formats can be easily defined using a point and click interface. This includes the ability to translate market symbols from one symbol set to another.
• For those traders that want tighter integration, TheBooks API (Application Programming Interface) has been extended to allow direct access to Data Manager data. This means your research platform and/or signal generator can read price series directly from TheBooks without the need to have the data exported to text files.

Even though DMAXX spent considerable effort on these enhancements, the features of the Data Manager that had set it apart from other data management applications have been retained and enhanced as well. These include:

• The ability to use actual trading history as a roll rule when building continuous contracts.
• The ability to define export sets which allows multiple data sources to be exported together to a single location in your network and to flag them to be automatically exported anytime any of the underlying data sources change.
• Multiple price data vendor support. This includes CSI, CQG, and eSignal, in addition to user-defined text file imports. This means you can easily use data from the vendor(s) which make the most sense for your research and trading.
• Flexible data download scheduling allows you to define as many scheduled downloads as you like. The data is downloaded in the background and is performed automatically. Continuous contracts and derived data sources are automatically updated and, as described above, any exports that are required are automatically performed as well.

Derived Data Sources
Derived Data Sources are an exciting new feature introduced in the 2.2.1 release of TheBooks. A Derived Data Source is a Data Source whose underlying data comes from one or more other Historic, Continuous Contract, or Derived Data sources combined using user-defined or pre-defined transformations. Once a Derived Data Source has been constructed, it can be used to like any historic data source or continuous contract.

There are four types of Derived Data Sources:

Ratio
A Ratio data source is simply the ratio of two underlying data sources. The numerator bar is divided by the denominator bar. This can be used to provide a relative strength data stream between the two instruments or can be used to create FX cross rates from two data streams of FX rates.

Index
An Index data source is the weighted sum of all the underlying data sources. For example, a user could define an Index-type derived data source as being a basket the major long-term interest rate futures contracts (US and foreign) and weight each as desired. The data stream would be the weighted sum of each of the underlying stream’s Open, High, Low, and Close. The trader could then track the index and Buy/Sell the basket of contracts as appropriate.

Term-Infused Spot
A Term-Infused Spot data source is a way to insert the term structure of continuous contract futures data into a data stream of daily spot prices. This provides a way to produce both outright and cross-rate historic price data that contains both a valid daily price range and term structure where currently the historic data does not exist. It uses an Historic Data Source which is an outright or cross-rate spot rate and a Continuous Contract.

User-Defined
A user-defined data source is a way for the user to apply his/her own transformations between two or more data sources. These transformations can include logical as well as numeric expressions, allowing to you perform operations such as splicing two data streams together at a specific date or comparing data streams from multiple sources and using the values from the stream that make the most sense on a given date within the data.

Summary
The Data Manager has evolved into a premier data management tool for the professional futures trader and researcher. These new features and those to come are designed to facilitate better research and better trading. It you are not using the data manager in your operation, now is the time to review its capabilities again.