« Customized Trade FilesTear Sheets made easy »

Using Excel to access TheBooks data

05/07/2010

Permalink 10:29:33 am, by Dana Comolli Email , 1058 words   English (US)
Categories: Futures Trading, Backoffice, Marketing, Data Access

Using Excel to access TheBooks data

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.

Begin defining a database

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.

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.

SQL Server Login dialog

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.

Create new data source

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.

Select the data source

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.

Table Options

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.

Query Wizard - Choose Columns

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.

Query Wizard - Filter Data

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.

Query Wizard - Sort Order

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.

Query Wizard - Choose Columns

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.

Query Wizard - Choose Columns

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.

Excel data loading progress

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

Pivot Table Designer

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.

Pivot Table

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.

Group Pivot Table Data

Group Pivot Table Data Prompt

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

Group By Month Pivot Table Data

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.

Charted Pivot Table Data

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.

June 2019
Mon Tue Wed Thu Fri Sat Sun
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
The official blog for users and others interested in TheBooks®

Search

XML Feeds

powered by b2evolution