Finance Experimental IEOR Department Mike Lipkin, Pankaj Mody
Housekeeping
Lab/home connectivity? Problem Set 2 due next week Office Hours
5:15pm to 8:00pm in Rm 318 (if the main door is locked, call 212-854-2987)
Also due next week, ONE person from each group, please email Pankaj ([e-mail]) and Marco ([e-mail]):
List of team members and their UNI Ids
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 2
Outline
Tables Select Statements Joins and Views Indices
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 3
Some Basic Database Design Background Four Basic Building Blocks: Tables: Information is split into multiple tables in order to minimize redundant information storage and to maximize speed of data return Indices and Keys: Organizing indices in an efficient manner so that queries return data quickly Views: Aggregation of table information into one virtual table Stored procedures and Functions: Similar to functions in C can pass data by reference or by value. Have the added capability of returning a query result.
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 4
Tables and Normalization
Normalization
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 5
Table Structures
Tables are normalized and split up for the following reasons:
Reduce storage by eliminating redundancy Querying smaller datasets is always faster Data management and storage for real-time systems: Data of the same type (i.e. same columns) can be split into multiple tables and stored on different drive clusters with their own controllers for simultaneous access.
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 6
Database Keys
Tables must have primary keys so that each row is uniquely identifiable. A primary key may consist of one or more columns SECURITY SECURITY_PRICE
Key: SecurityID
Key: SecurityID, Date
OPTION_PRICE
Key: SecurityID, Date, Expiration, Strike, callPut
Experimental Finance Mike Lipkin, Alexander Stanton Page 7
Database Keys - continued Keys can be based on columns that contain information e.g. Social security number ISBN number for books Is the ticker symbol a good key choice?
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 8
Database Keys - continued Keys can be based on columns that contain information e.g. Social security number ISBN number for books Is the ticker symbol a good key choice? No. Any data that has a remote possibility of changing is never a good candidate for a primary key Historical data would require time-consuming updates when a corporate name change occurs Existing queries and stored procedures might break if keys are referenced in any way (say as part of temporary or result tables)
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 9
Database Keys Additional reasons not to use data elements as primary keys:
Possibility of nulls (options may need to be created in a database before they have an official exchange ID or value) Changes in data structures and naming conventions Varying length data and size limitations
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 10
Database Keys Additional reasons not to use data elements as primary keys:
Possibility of nulls (options may need to be created in a database before they have an official exchange ID or value) Changes in data structures and naming conventions Varying length data and size limitations
A good financial example of a really bad key choice: IBMBL.X 60 Feb 2007 Call Last: 37.50 OI: 1451
After IBMBL.X expires, it is re-used. This causes major headaches during analysis, especially when the information to reconstruct the option is not available
Experimental Finance Mike Lipkin, Alexander Stanton Page 11
Surrogate Keys
Surrogate keys are used to manage relationships between data where even the defining data is likely to change
Effective Date
securityID stays constant with stock and option prices for the same company
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 12
What really happened?
Citibank used to be CCI before it merged with TRV C merged with Daimler to become DCX When Citibank merged with Traveler s Group it stayed CCI for two months before changing to C, about one month after Chrysler merged with Daimler. C was Chrysler it is now Citibank TRV is now Thousand Trails Inc. CCI is now Crown Castle Intl.
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 13
SQL Syntax
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 14
SQL Syntax Transact SQL is the database language pronounced Sequel Most databases use SQL to manipulate data Microsoft SQL Oracle Postgres MySQL
There are varying formats for SQL, but differences are minimal: e.g. return the top 10 rows: SELECT ticker FROM security limit 10; (MySQL) SELECT TOP 10 ticker FROM security; (MS-SQL) SELECT ticker FROM security WHERE ROWNUM < 10; (Oracle)
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 15
SQL Syntax
SQL has 2 parts DML Data Manipulation Language
Commands which retrieve from, update, delete or insert rows into a table, e.g.
SELECT UPDATE DELETE INSERT INTO
DDL Data Definition Language
Commands which define and update database objects such as tables, views, indexes and entire databases, e.g.
CREATE TABLE CREATE VIEW DROP TABLE CREATE INDEX
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 16
SELECT Queries
Basic Format SELECT <columns> FROM <table> WHERE <condition> ORDER BY <columns>
e.g.
SELECT securityID FROM security WHERE ticker= MSFT
result: 1051334
The keyword DISTINCT can be used to remove duplicates: SELECT DISTINCT strike FROM option_price (for MSFT in 2006)
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 17
WHERE Clause WHERE clause can use several operators: > < = AND, OR, LIKE, BETWEEN, IS NULL, IN, NOT e.g. SELECT securityID, date, closePrice FROM security_price WHERE close_price>200 AND date BETWEEN 2005-01-01 AND 2005-01-03 ORDER BY securityID, date
Returns all closePrices for stocks that have a price greater than $200, ordered first by ticker, then by date
Experimental Finance Mike Lipkin, Alexander Stanton Page 18
GROUP BY Clause
GROUP BY clauses create groupings when using aggregate functions such as:
AVG(), SUM(), COUNT(), MAX(), MIN()
e.g.
SELECT ticker, date, MAX(closePrice) as maxClosePrice, (weekNumber) FROM security_price WHERE date BETWEEN 2005-01-01 AND 2005-01-03 GROUP BY ticker, (weekNumber) ORDER BY ticker, (weekNumber)
Instructs SQL to return the maximum close price for each week for each ticker (i.e. grouped by ticker and week )
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 19
GROUP BY Clause Using a Count
SELECT securityID, date, count(*) FROM option_price GROUP BY securityID, date ORDER BY securityID, date Desire the number of different strikes across all series per day
What might be wrong here?
Experimental Finance Mike Lipkin, Alexander Stanton Page 20
Fixed Count
SELECT securityID, date, count(*) FROM option_price WHERE callPut = call GROUP BY securityID, date ORDER BY securityID, date
day per
Returns the number of strikes across all expirations per ticker
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 21
IN Clause IN Clause: SELECT ticker, date, closePrice FROM security_price WHERE ticker IN ( MSFT , MT , INTL , KO ) AND date BETWEEN 2005-01-01 AND 2005-01-03 ORDER BY date, ticker Returns all closePrices for the four stocks in the date range, ordered first by date, then ticker.
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 22
Sub Queries:
SELECT ticker, date, closePrice FROM security_price WHERE ticker IN (SELECT ticker FROM myTickerTable) AND date BETWEEN 2005-01-01 AND 2005-01-03 ORDER BY date, ticker
Very useful speed up strategy for repeated querying: Instead of joining two large tables to find the intersection, and running this repeatedly, create a small table of the intersection and then use either a JOIN or a sub-query using IN for further queries
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 23
SQL JOINS
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 24
JOINS
Joins allow combining of data from disjoint tables based on keys Allow conditional selection of data from one table that matches (or doesn t match) conditions in another Join conditions can be based on primary keys, unique and nonunique keys, as well as generic data in the table Joins can be complicated they are highly susceptible to design errors, and can lead to exceedingly long query times and incorrect data
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 25
JOINS
Stock Price
Option Price
Stock & Option Data
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 26
JOINS
Basic Format SELECT <columns> FROM <table1> INNER JOIN <table2> ON <condition> WHERE <condition> ORDER BY <columns> Returns a Cartesian product of table1 and table2, N x M rows <Conditions> can have multiple components Beware!! Joins where the condition is not precisely matched can cause large record sets of garbage data Usage of NULL is very powerful
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 27
INNER JOINS JOIN tables based on one or more conditions Result is an intersection of both tables where JOIN condition is satisfied SELECT ticker, date, closePrice FROM security s INNER JOIN security_price sp ON s.securityID=sp.securityID
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 28
INNER JOINS SELECT securityID, sp.date, sp.closePrice, o.strike, o.closePrice FROM security_price sp INNER JOIN option_price o ON o.securityID=sp.securityID
What is wrong here?
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 29
Cartesian Products JOIN conditions result in Nmatch X Mmatch Rows It is critical to get them right: SELECT ticker, sp.date, sp.closePrice, o.strike, o.closePrice FROM security_price sp INNER JOIN option_price o ON o.securityID=sp.securityID AND o.date=sp.date
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 30
JOIN Conditions- continued Note that this is not (quite) the same as: SELECT ticker, sp.date, sp.closePrice, o.strike, o.closePrice FROM security_price sp INNER JOIN option_price o ON o.securityID=sp.securityID WHERE o.date=s.date WHY?
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 31
JOIN Conditions- continued Note that this is not the same as: SELECT ticker, sp.date, sp.closePrice, o.strike, o.closePrice FROM security_price sp INNER JOIN option_price o ON o.securityID=sp.securityID WHERE o.date=s.date WHY? The processing order is: FIRST: Joins evaluated SECOND: results restricted by WHERE clause The query above creates much more data, most of which is false, and then trims out the false results. Highly inefficient. (NOTE: some SQL engines are good at parsing and optimizing, but this is fundamental knowledge you should have. The DB might get it wrong so the more you know the better)
Experimental Finance Mike Lipkin, Alexander Stanton Page 32
OUTER JOINS
JOINS OUTER select all data from one table, and only data from the second table where the JOIN condition is matched: SELECT ticker, date, closePrice FROM security s LEFT OUTER JOIN security_price sp ON s.securityID=sp.securityID
Joins are evaluated first, then the results are restricted according to the WHERE clause Can use LEFT OUTER JOIN and RIGHT OUTER JOIN to specify which table will be returned in full Columns values from the second table that don t match are filled with a NULL
Experimental Finance Mike Lipkin, Alexander Stanton Page 33
OUTER JOINS SELECT ticker, date, closePrice FROM security s LEFT OUTER JOIN security_price sp ON s.securityID=sp.securityID
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 34
Power of Nothing
OUTER JOINS are very powerful in conjunction with the NULL value. Example: SELECT TOP 1 ticker FROM security s LEFT OUTER JOIN security_price sp ON s.securityID=sp.securityID WHERE sp.date=NULL
Result:
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 35
Unfortunate Syntax
Table1 LEFT JOIN table 2 ON table1.ID = table2.ID Table1 LEFT OUTER JOIN Table2 ON table1.ID = table2.ID Table1, Table2 WHERE table1.ID = table2.ID Bad Syntax confuses WHERE clause Table1 OUTER JOIN Table2 ON table1.ID = table2.ID Bad Syntax only time the order of the tables matters
Use Standard ANSI-92 Notation: Table1 LEFT OUTER JOIN Table2 ON table1.ID = table2.ID
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 36
FULL JOINS and CROSS JOINS
FULL JOINS evaluate both tables and return all rows from both tables
CROSS JOINS return the cartesian product of two tables where there is no common key (i.e., no ON clause)
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 37
UNIONs UNIONs join tables with identical tables structures: SELECT * FROM OPTION_PRICE_2005_01 UNION ALL SELECT * FROM OPTION_PRICE_2005_02
2005_01
2006_02
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 38
Self JOINS
SQL processing is entirely row based There is no knowledge of previous or future row values One solution is to use JOINS to join a table to itself
SELECT s.ticker, sp1.date, sp1.closePrice, sp2.date, sp2.closePrice FROM security s INNER JOIN security_price sp1 on sp1.ID = s. ID LEFT JOIN security_price sp2 on sp2.ID = sp1.ID AND sp2.date=sp1.date-1 WHERE ticker='MSFT' AND sp1.date between '2004-01-07 and '2004-01-12
Why the LEFT JOIN?
Experimental Finance Mike Lipkin, Alexander Stanton Page 39
Self JOINS
Why would this query be useful?
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 40
Self JOINS
Why would this query be useful?
Compute the log return: SELECT s.ticker, sp1.date, (sp1.close-sp2.close)/sp2.close AS logReturn
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 41
Comparing Stock Price Differences
select sp1.date, s1.ticker, sp1.closePrice, s2.ticker, sp2.closePrice, (sp1.closePrice-sp2.closePrice) as diff from ivydb..security_price sp1 inner join ivydb..security_price sp2 on sp1.date=sp2.date inner join ivydb..security s1 on s1.securityID=sp1.securityID and s1.ticker='MOT' inner join ivydb..security s2 on s2.securityID=sp2.securityID and s2.ticker='NOK' where sp1.date>'2004-01-01'
Experimental Finance Mike Lipkin, Alexander Stanton Page 42
Example: Price Differences
MOT vs. NOK price difference 1/2004-5/2005 8 6 4 2 0 10/6/03 -2 -4 -6 -8
1/14/04
4/23/04
8/1/04
11/9/04
2/17/05
5/28/05
9/5/05
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 43
VIEWS
Views are based on SELECT statements and offer a shortcut to dealing with data that has already been normalized Views are identical to tables in terms of querying. Each base table can and will be evaluated based on its own indices Views are generally non-updateable, but they do exist IVY has a large UNION query for option prices OPTION_PRICE_VIEW Views can be indexed (allows precompilation) CREATE VIEW myView AS SELECT {} FROM {} INNER JOIN {} INNER JOIN {} INNER JOIN {} INNER JOIN {} INNER JOIN {} WHERE {} Etc. SELECT ticker, date, logReturn FROM myView ORDER BY ticker, date
Experimental Finance Mike Lipkin, Alexander Stanton Page 44
Large Queries
SELECT sp.date, sp.closePrice, dbo.formatstrike(strike) as strike, expiration, callput, datediff(day,sp.date,expiration) as daysToExpiration, CAST(datediff(day,sp.date,expiration) as float)/360 as interestDays, dbo.mbbo(bestBid,bestOffer) as mbbo, PPOP = (case WHEN dbo.formatstrike(strike)<sp.closePrice THEN dbo.mbbo(bestBid,bestOffer) ELSE dbo.mbbo(bestBid,bestOffer)-(dbo.formatstrike(strike)-sp.closeprice) END), CPOP = (case WHEN dbo.formatstrike(strike)>sp.closePrice THEN dbo.mbbo(bestBid,bestOffer) ELSE dbo.mbbo(bestBid,bestOffer)-(sp.closeprice-dbo.formatstrike(strike)) END) FROM security s INNER JOIN security_price sp ON s.securityID=sp.securityID INNER JOIN option_price_view o ON o.securityID=s.securityID AND sp.date=o.date INNER JOIN option_price_view o2 ON o2.securityID=s2.securityID AND sp.date=o2.date-1 WHERE sp.date>='2001-01-01 00:00:00' AND sp.date<='2005-01-01 00:00:00' AND Ticker IN (SELECT ticker FROM portfolioTickers pt INNER JOIN option_price_view o3 ON pt.securityID=03.securityID WHERE putCall= P ) AND abs(sp.closeprice-dbo.formatstrike(strike))<=7.5 ORDER BY sp.date, strike, expiration, callput
Experimental Finance Mike Lipkin, Alexander Stanton Page 45
SQL is full of Gotchas
What is wrong here (this is an Oracle example): SELECT t.* FROM trades t WHERE ROWNUM < 6 ORDER BY date DESC
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 46
SQL is full of Gotchas
What is wrong here (this is an Oracle example): SELECT t.* FROM trades t WHERE ROWNUM < 6 ORDER BY date DESC
Corrected: SELECT * FROM (SELECT t.* FROM trades t ORDER BY date DESC) WHERE ROWNUM < 6
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 47
Indices
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 48
Indices
Indices are added to tables to quickly access results when asking for a subset Primary keys are inherently indexed
e.g. SELECT all option data for Microsoft on 2005-05-12 will be an indexed query Lets say, however, that we want to return all days where the volume was above 80,000,000 for any stock The database would not be able to use the same index since neither Date nor SecurityID is part of the query restriction
Experimental Finance Mike Lipkin, Alexander Stanton Page 49
Table Scans, or Why the Database Dies Table scans Every row in the table is traversed and compared to the requirement volume>80,000,000 , then the list is compiled and returned. With 642,000,000 rows this will not even happen overnight Resolution Define a index on the database based on the volume column This creates an ordered list that can be searched using a B-Tree quickly and efficiently. It is hardly ever necessary to use a table scan for historical data be careful!!
Experimental Finance Mike Lipkin, Alexander Stanton Page 50
The Big Catch The database can only use one index per operation Consider: SELECT all options FROM optionsTable WHERE ticker= MSFT AND optionVolume>10,000 The database has two options: Use the primary key index (securityID, date) to select only MSFT data, then do a scan on the subset Use the volume index to get all option data that has volume>10,000, then scan to return only MSFT data Which is better?
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 51
The Query Optimizer Query Plans Answer: It depends
Limiting to MSFT means that we can reduce the dataset by a factor of 5,000 using the primary key If MSFT strike volume is only above 10,000 very infrequently, and there are only a few other stocks that have a similarly high volume, the volume index will be more efficient.
The built in SQL Query Optimizer decides which index is better to use, based on hit ratios and other statistics This is a statistical process and the optimizer may choose a different query plan for the same query depending on circumstance A small change in the query (e.g. volume> 11,000) might cause it to change the query plan and use a different index, yielding completely different execution times
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 52
Query Plans are not Voodoo Look at the query plan before the query executes: set showplan_all [on|off] Displays the query plan, i.e. what steps the engine will use to execute the query. The query is not actually executed. Check the query will use the indices you expect it to If a slightly modified query takes much longer, compare query plans. It is possible to give the query optimizer Hints (e.g. use the volume index ) however this is for experts only the query optimizer is generally better than you.
Do not let queries run out of control!
Experimental Finance Mike Lipkin, Alexander Stanton Page 53
Data Validation and Cleaning
Always validate your results spot check by selecting example rows and perform the entire calculations manually IVY generally has good data, but care must be taken to ensure analysis is not based on inconsistent data, mostly as a result of query selectivity. All data sources have problems don t forget it Check for:
abnormal values negative numbers rounding errors COUNT/SUM that count differently sized sets (e.g. 4-day weeks)
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 54
Useful Functions
IF THEN ELSE Statements CAST (arithmetic) and CONVERT (strings) Date Functions:
DATEADD DATEDIFF DATEPART MONTH YEAR
FLOOR, CEILING
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 55
Date Gotchas Nobody agrees on holidays, especially internationally
4th July Bank Holidays Easter in Gregorian/Julian calendars, Orthodox vs. Catholic Weekends in Islamic countries Germany s hard-working four-day work-week On July 4th, 2008 they announced that the world's atomic clocks would be adding another second at the end of the year
The Maritime Freight Industry publishes local holiday schedules for every port in the world We aren t so lucky, and even if we know the dates, interpreting pricing rules for instruments and comparing different markets is exceedingly difficult Dates are just an example: Currencies, Lots, Price Ticks all provide ample fun for your weekend coding
Experimental Finance Mike Lipkin, Alexander Stanton Page 56
ODBC Setup
Control Panel -> Administrative Tools -> Data Sources (ODBC) Add a File DSN, choosing the SQL server driver Connect to the Database in the same way as the Query Analyzer
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 57
Excel & Data Sources
In Excel, choose Data->Import External Data->New Database Query Choose the DSN you created, and write a query to select results
Experimental Finance
Mike Lipkin, Alexander Stanton
Page 58
Next Time
Creating Tables Creating Temporary Tables Creating Indices Stored Procedures and Functions
PROBLEM SET 2 DUE NEXT WEEK
Also... you might want to read the following paper in your free time: A Market-Induced Mechanism For Stock Pinning
Experimental Finance Mike Lipkin, Alexander Stanton Page 59