TheBooks

Have you ever run a report in TheBooks, and think to yourself, “This report would be perfect if I could just add this one additional column.” We’ve been listening! The latest version of TheBooks now lets you use TheBooks data with Microsoft SQL Server Report Builder to create and customize reports. You can integrate these reports into TheBooks Performance Reports interface. You can make the reports private, usable only by the person adding the report, or public, shared across your organization. You can add your custom logo and your company information, and format the report so that it is indistinguishable from any other reports generated by TheBooks. As with TheBooks standard reports, you can send these custom reports out in any format supported by TheBooks, and to any contacts you have defined within TheBooks.

Creating the Report

To create your custom report, you must first install the Microsoft SQL Report Builder application on your computer. To begin creating a report, open Report Builder and select the Table or Matrix icon.

You will select TheBooks as your data source, and then begin to design your query. You will select the entity you want to include in your report, and then the fields you want to include from within that entity.

Adding fields to your report is as simple as dragging them into the Drag and drop column fields section of the screen. You can also define computed fields to return information that is not available in an entity field. For example, to add net profit to our report, we would drag Change in OTE, Realized Gains, Charged Trading Commissions, and APS Adjustments into the formula bar, and then apply the appropriate operators.

You can arrange the fields into rows and columns, and decide if you want to perform any functions, such as total or average a particular field. The Report Builder allows you to add parameters so that you can select a specific set of data to be returned in your report. In addition, you can add filters at the data source, so that only the data that matches our parameters is returned. This allows the report to run more efficiently.

Integrating Your Report into TheBooks

Once you are satisfied with your results in Report Builder, you can then integrate your new report into TheBooks Reports interface. Go to Reports and select the Manage Custom Reports option. You will add the report to TheBooks from this screen, enter the Report Name, a Description of the report, and determine the Visibility, that is, whether it is a public or private report.

Your report now appears on the Performance Reports screen, under the Custom Reports tab. You can now select your Options, which have been determined by the parameters you created in Report Builder, and generate this report from within TheBooks.

The Finished Report

This is a sample of an Instrument Returns by Month report that was created in Report Builder, and integrated into TheBooks. Parameters were defined to specify an account, an account group, and a specific time period. Net profit was calculated for each market grouped by month and totaled by sector. The company logo and company information were added to the header and footer of the report.

Summary

I have shown how easy it is to produce custom reports from within Report Builder using the data produced by TheBooks. Creating the reports and integrating them with TheBooks Reports does not require any programming experience as all the necessary capabilities are provided by Report Builder and TheBooks.

One aspect of trading managed accounts which can cause a CTA endless headaches is the production of end of day trade files to the various administrators and back offices that are associated with the accounts.

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.

TheBooks automatically summarizes a wide range of trading and performance data in formats that are easily accessible to users of common office application such as Microsoft Access and Excel. TheBooks comes with a number of examples of how to access this data using its programming interface.

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.