Forums  > Software  > CSI UA API  
     
Page 1 of 4Goto to page: [1], 2, 3, 4 Next
Display using:  

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-03-19 05:38

This post is for those using CSI as their source of daily data. I mentioned in another thread that I would play around with the API and post what I came up with. I have not spent much time developing anything fancy, but here is a very simple Perl script that can pull all of the futures contracts for a CSI identification number (in this case NG=191) and fetch the historical data for each contract. There is no error checking yet, but this example demonstrates how easily this sort of thing can be done. This approach employs the clipboard so it has some clear limitations. Anyway, in a few days I should have something simple to maintain a MySQL database. I will share with anyone interested.

Attached File: demo_fetchFuturesCSI.zip


...WARNING: I am an optimal f'er

apine


Total Posts: 1008
Joined: Jun 2004
 
Posted: 2007-03-20 02:08
so presumably, the api will allow the user to pull out daily updates to bring into mysql or sqlserver.

have you addressed how to deal with corrections? say that there was an error in the high price from 3 days ago. is there the potential to handle this?

Too many people make decisions based on outcomes rather than process. -- Paul DePodesta

apine


Total Posts: 1008
Joined: Jun 2004
 
Posted: 2007-03-20 02:08
and thanks for putting up the post.

Too many people make decisions based on outcomes rather than process. -- Paul DePodesta

tristanreid


Total Posts: 1677
Joined: Aug 2005
 
Posted: 2007-03-20 03:14

Kewl, dood. Thanks for posting.

To bypass the clipboard, use CopyRetrievedDataToArray2. You pass in an array and it gets populated with results.  Check out section 4.3.1.15 of

Attached File: UaOleApi.doc

Also, if you're not big into SQL or working the database, let me know. It's not a big thing on NP, but it's kind of a big part of my job. I'm currently using MySQL, Oracle, and Sybase.  MySQL is a great choice.

-t.


the only reason it would be easier to program in C is that you can't easily express complex problems in C, so you don't. -comp.lang.lisp

Tradenator


Total Posts: 1576
Joined: Sep 2006
 
Posted: 2007-03-20 03:32
WorshipApplauseChug Beer

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-03-20 10:18

Thanks tristanreid. MySQL is a big part of my job as well so I am fairly comfortable with it, but I will definitely post my next (hopefully more complete) script and I am fairly certain you can do 10x-100x better. When it comes to programming I am a hack. 

I didn't remember seeing the CopyRetriedDataToArrary2 in the UaOleApi.doc so I will most definitely replace with that.

Alpine, I usually use MySQL's innoDB tables because they make revisions really easy to deal with. With daily data the upload happens almost instantly.

You can set a table up like this:

#-------------------------------------------------------------------------------------------
# Create time & sales table + quote
#-------------------------------------------------------------------------------------------
sub setup_fx_quote_table($$){
my ($db_handle,$statement_handle,$symbol);
$db_handle=shift;
$table=shift;

# define the table statement
$statement_handle=$db_handle->prepare("CREATE TABLE $table (
db_update_timestamp TIMESTAMP,
ticker VARCHAR(40) NOT NULL,
datetime_id INT UNSIGNED NOT NULL,
datetime DATETIME NOT NULL,
bid_price DECIMAL(20,10) NOT NULL,
bid_exchange VARCHAR(10) NOT NULL,
ask_price DECIMAL(20,10) NOT NULL,
ask_exchange VARCHAR(10) NOT NULL,
PRIMARY KEY (ticker,datetime_id,datetime,bid_price,bid_exchange,ask_price,ask_exchange)) TYPE=INNODB");
# may need to create a second table that indexes the table to avoid dublication

# execute the statement using the statement handle
$statement_handle->execute();

# finish the statement execution
$statement_handle->finish();
print "...statement execution complete...\n";
}

Create an index like this:

#-------------------------------------------------------------------------------------------
# Add an index on datetime and datetime_id LOAD DATA
#-------------------------------------------------------------------------------------------
sub index_tick_data_table{
my ($db_handle, $table_name)=@_;
my $sql = "ALTER TABLE $table_name ADD INDEX (datetime,datetime_id);";
my $count=$db_handle->do($sql);
# print row count if no error occurred
if($count){ $count+=0; print "$count fx quote records were updated in $table_name\n";}
}

Then you can bulk load it like this:

#-------------------------------------------------------------------------------------------
# Bulk loading of fx quote data employing LOAD DATA
#-------------------------------------------------------------------------------------------
sub load_data_fx_quote{
my ($db_handle, $input_directory, $input_file, $table_name)=@_;
my $sql = "LOAD DATA INFILE '$input_directory/$input_file' IGNORE INTO TABLE $table_name FIELDS TERMINATED BY ',' (ticker,datetime_id,datetime,bid_price,bid_exchange,ask_price,ask_exchange);";
#print "$sql\n";
my $count=$db_handle->do($sql);
# print row count if no error occurred
if($count){ $count+=0; print "$count fx quote records were updated in $table_name\n";}
}

The IGNORE INTO TABLE command ignores all unchanged data and updates anything that has changed since the last update. With daily data the database is very straightforward.


...WARNING: I am an optimal f'er

Patrik
Founding Member

Total Posts: 1333
Joined: Mar 2004
 
Posted: 2007-03-23 00:05
Somewhat unrelated but I'll post it anyways in case it's useful to someone. I once found myself in a situation where we had CSI data but ended the subscription and the application refused to deal with the historical data we already bought (and no good backups...). I spent some time on it and hacked the application to deliver already stored and purchased data after the time limit ran out. Think I still have it around somewhere if anyone's in need of such a thing.

Capital Structure Demolition LLC Radiation

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-03-23 02:19

please post that if it isn't too much trouble to dig up.

Thanks!


...WARNING: I am an optimal f'er

Patrik
Founding Member

Total Posts: 1333
Joined: Mar 2004
 
Posted: 2007-03-23 09:29
Ok, I'll try to find it.

Capital Structure Demolition LLC Radiation

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-01 21:36
Is anyone using the futures options data available from CSI? I am just wondering able the quality of the data.

...WARNING: I am an optimal f'er

Tradenator


Total Posts: 1576
Joined: Sep 2006
 
Posted: 2007-04-01 23:27

Having sifted ad nauseum through data, my opinion is that CSI easily kick the asses of Bloomberg and Factset for futures data.  I currently use CSI, and expect to continue for a long time.

Having said that, a Bloomberg guy said they were supposed to be re-acquiring commodity futures data from the exchanges in a big project to lift their game.  This included the "tickerization" of LME 3rd Wednesday contracts.  This was mid 2006, and I never followed up on their progress.


dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-02 04:04

I find it hard to compare Bloomberg with CSI because Bloomberg has so much data, but in general I hate Bloomberg. The interface bugs me and the Excel downloading tools seem to malfunction regularly.

Anyway, I have also done my time sifting through data and I have found CSI to be very good when compared to all the data sources I have had access to over the years. I have DTN IQFeed & eSignal as well, but I use them for collecting time & sales. DTN has 10 days of option tick data, but the granularity on the time stamp is only to the minute.

Anyway, I don't have the CSI futures options data, but I will probably get it sooner or later.


...WARNING: I am an optimal f'er

Tradenator


Total Posts: 1576
Joined: Sep 2006
 
Posted: 2007-04-02 06:05

Continuing on the API theme, I am close to finishing a python module that will grab UA data via the API, calculate contract returns for a continual series (rolls on volume), and write to csv file.  I can see that some of this (data handling) should be compiled in C but am not taking that extra step at this time.  Instead, my next step is to push individual contracts into PostgreSQL, so for now writing to csv will do.  If there is any interest in the csv version, let me know and I can email you.


dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-02 13:00
It sounds like we are doing similar things but with slightly different tools. I just got my CSI futures data into MySQL yesterday via Perl. Creating constant maturity forward points is my next task but I need to experiment to find the right approach.

...WARNING: I am an optimal f'er

Johnny
Founding Member

Total Posts: 4333
Joined: May 2004
 
Posted: 2007-04-02 14:03

I use CSI UA as well and find it very clean.

dgn2, out of interest, why are you looking at constant maturity forward points rather than roll dates or roll by volume or similar?


heartbreakingly simple

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-02 18:01

I haven't ruled out roll dates or roll by volume but I guess because I started in rates and you had curve objects and instruments quoted at constant maturity points, constant maturity forward points are a natural point of reference for me. I am used to looking at things that way. I figure I will build the usual swap curves and associated forwards and use them to get a feeling about how different forward rates are for different commodities. I don't think I will necessarily settle on that approach but I can maybe isolate the effect of time on carry that way and better understand non-financial commodities.


...WARNING: I am an optimal f'er

Johnny
Founding Member

Total Posts: 4333
Joined: May 2004
 
Posted: 2007-04-02 19:02

Fair enough - thanks for the explanation :)


heartbreakingly simple

FDAXHunter
Founding Member

Total Posts: 8335
Joined: Mar 2004
 
Posted: 2007-04-02 19:05
What explanation? "The usual swap curves on commodities"? So how does a plain vanilla crude swap, work, exactly?

Ottawa is messing with your brain dude. Better move.

The Figs Protocol.

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-02 23:34

FDAX, please re-read what I wrote. I did not say anything about swaps on commodities although I did mention swaps. I meant interest rate swaps and I think we can agree that the swap curve represents interbank rates. I just want to know how the carry on different commodities relates to interest rates...and I have a feeling you knew what I meant...


...WARNING: I am an optimal f'er

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-03 00:11

I am just starting to look at commodities. I am not involved - and never have been involved - with commodities so I am starting by checking how close the spreads trade to full carry. I read this shit in a bunch of books and I freely admit I have no practical experience with non-financial commodities. At this point I am just curious. Does that not make sense? I was just wondering how much of the carry on Natural Gas for instance is based on financing? How are those financing rates related to the interbank interest rate market? How much is related to transportation between hubs? There appear to be prices on basis instruments and I am just curious about how much information there is. Anyway, I am curious about the term structure I will end up building a constant maturity forward curve.

Do people typically not try to break the carry out into different components on something like Natural Gas? Contracts are listed out to 2012-12 although the back months don't look like they have much open interest.


...WARNING: I am an optimal f'er

TonyC
Nuclear Energy Trader

Total Posts: 1242
Joined: May 2004
 
Posted: 2007-04-04 17:44
if oil is is in carry, my tanks are full, if oil is backwardated, my tanks are empty [or close too it]

if natural gas is backwardated, and its anytime twixt may and sept, there is a good chance i'm storing/injecting gas; even though its unecomomic . . . cause i don't want the salt dome to collapse.

natural gas is the worst sort of thing for the newcomer to try and model in terms of carry

try heating oil, its got a simple seasonality, [i.e. not natty's bimodal heating & air-conditioning demand seasonality] . . try regressing the nymex backwardation against nymex vs platts gulf coast [nymex prompt vs, oh, gulf coast 2 or 3 cycles out to account for 15-21 day transport up the pipeline]

flaneur/boulevardier/remittance man/energy trader

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-04 19:58
Thanks TonyC! I will definitely try everything you have mentioned. Right now I am just trying to get a very rough feel for things. I am a long way off anything usable.

...WARNING: I am an optimal f'er

Patrik
Founding Member

Total Posts: 1333
Joined: Mar 2004
 
Posted: 2007-04-05 02:31
As TonyC illustrates the commodities markets have a lot of spreads that people focus on, be it time spreads (i.e. buy cheap summer gas for storage and forward sell winter gas to lock in spread), geographical spreads (i.e. buy crude in one region + freight, sell in another and lock in a positive spread) or quality spreads (i.e. higher sulphur content coal is worse but cheaper, but perhaps you can improve your cleaning technology and use cheaper coal to improve overall margins). Time value of money and a link to rates markets comes into consideration in many cases, but it's seldom the major driver - i.e. if you use 4.4% or 4.6% rates for something isn't your biggest worry.

Even in a market like CO2 emission certificates you see deviations to a pure cost of carry reasoning. These certificates are fungible within a phase (5y period), and they exist only as information in a system, so pretty close to financials. Market trades at a yearly contango of about 50-55cents when full cost of carry should be closer to 70cents.

A constant maturity forward curve in highly seasonal markets will have some funny dynamics, not quite sure of applications where I'd find it useful. I'm curious - what did you have in mind?
Think of it this way - for example in the UK winter gas and summer gas could almost be seen as separate assets at times - an analogy could be two stocks that are part of the same index but in different completely different sectors or something.

P.S. Looks like my UA hack may have been lost in a computer move, but haven't fully given up yet.

Capital Structure Demolition LLC Radiation

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-05 15:27

Interesting.

I was thinking about decomposing the forward curve into seasonal and non-seasonal components, but I have not looked at this stuff very closely yet so I am not sure whether what I am thinking will provide any value. I saw this in an introductory book on commodity derivatives and I figured I would learn something working through a few simple examples. The seasonality does interest me because I work with signal processing guys who can potentially do useful things with signals that show some regularity. My objective is to deseasonalize the curve as part of the constant maturity building process so that I have something additive. I have to experiment with the data to figure out how well this works before I can attempt comment intelligently.

Anyway, don't worry about the UA hack.

 


...WARNING: I am an optimal f'er

dgn2


Total Posts: 2047
Joined: May 2004
 
Posted: 2007-04-12 06:47

I ended up getting the futures options data so I wrote some rough code to pull the data. I figured I would post it for our CSI for phamily members:use Time::Local;
use Getopt::Long;
use Win32::OLE;
use Win32::Clipboard;
use Win32::OLE::Variant;

#===============================================================================
# Notes
#===============================================================================
# -fetch Natural Gas, Oil, Eurodollars, Euribor, short sterling
# -load all contracts to MySQL symbol table
# -get all of the unique dates
# -for each unique date, fetch all of the contracts with data and construct a curve
#

Win32::OLE::CreateObject("UA.Api2", $ua) || die "unable to create UA.Api2 object: $!";
#$ua->UpdateDatabase();
#$ua->{HoldUAOpenOnClose} = 0;
#undef($ua);

#-------------------------------------------------------------------------------
#
#-------------------------------------------------------------------------------
my %FUTURES_OPTIONS = (
"Eurodollar-3 Mth-CME-Globex(Floor+Electronic Combined)" => "141",
"Gas-Natural Henry Hub-NYMEX(Floor Trading Only)" => "191",
"Petroleum-Crude Oil Light-NYMEX(Floor Trading Only)" => "188",
"Intrbnk Offer Rate-EURIBOR-3 Mth-EURONEXT(LIFFE)" => "565",
"Sterling Rate-3Mth-EURONEXT(LIFFE)" => "173",
"Euro Swiss Franc-EURONEXT(LIFFE)" => "185"
);
#-------------------------------------------------------------------------------
#
#-------------------------------------------------------------------------------
my %h2hh = (
"1" => "01",
"2" => "02",
"3" => "03",
"4" => "04",
"5" => "05",
"6" => "06",
"7" => "07",
"8" => "08",
"9" => "09",
"10" => "10",
"11" => "11",
"12" => "12"
);
#-------------------------------------------------------------------------------
#
#-------------------------------------------------------------------------------
my %yy2yyyy = (
"97" => "1997",
"98" => "1998",
"99" => "1999",
"00" => "2000",
"01" => "2001",
"02" => "2002",
"03" => "2003",
"04" => "2004",
"05" => "2005",
"06" => "2006",
"07" => "2007",
"08" => "2008",
"09" => "2009",
"10" => "2010"
);
#-------------------------------------------------------------------------------
#
#-------------------------------------------------------------------------------
my %yyyy2yy = (
"1997" => "97",
"1998" => "98",
"1999" => "99",
"2000" => "00",
"2001" => "01",
"2002" => "02",
"2003" => "03",
"2004" => "04",
"2005" => "05",
"2006" => "06",
"2007" => "07",
"2008" => "08",
"2009" => "09",
"2010" => "10"
);
#-------------------------------------------------------------------------------
#
#-------------------------------------------------------------------------------
my %monthnum = (
"Jan" => "01",
"Feb" => "02",
"Mar" => "03",
"Apr" => "04",
"May" => "05",
"Jun" => "06",
"Jul" => "07",
"Aug" => "08",
"Sep" => "09",
"Oct" => "10",
"Nov" => "11",
"Dec" => "12"
);
#-------------------------------------------------------------------------------
#
#-------------------------------------------------------------------------------
my %nummonth = (
"01" => "Jan",
"02" => "Feb",
"03" => "Mar",
"04" => "Apr",
"05" => "May",
"06" => "Jun",
"07" => "Jul",
"08" => "Aug",
"09" => "Sep",
"10" => "Oct",
"11" => "Nov",
"12" => "Dec"
);
#-------------------------------------------------------------------------------
#
#-------------------------------------------------------------------------------
my %daynum = (
"1" => "01",
"2" => "02",
"3" => "03",
"4" => "04",
"5" => "05",
"6" => "06",
"7" => "07",
"8" => "08",
"9" => "09",
"10" => "10",
"11" => "11",
"12" => "12",
"13" => "13",
"14" => "14",
"15" => "15",
"16" => "16",
"17" => "17",
"18" => "18",
"19" => "19",
"20" => "20",
"21" => "21",
"22" => "22",
"23" => "23",
"24" => "24",
"25" => "25",
"26" => "26",
"27" => "27",
"28" => "28",
"29" => "29",
"30" => "30",
"31" => "31"
);
#-------------------------------------------------------------------------------

################################################################################
#
################################################################################
sub getFuturesOptions($){
my $output_directory=shift;
print "$output_directory\n";
($day,$month,$year)=(localtime)[3,4,5];
my $yyyy=$year+1900;
my $mm=$h2hh{$month+1};
my $dd=$daynum{$day};

my $updateFile="optionsUpdate-$yyyy-$mm-$dd";

# open the daily download log
open(UPDATE,">$updateFile");

# iterate over the %FUTURES hash

while (($name,$futuresOptionsSymbol) = each(%FUTURES_OPTIONS)){

print "...getting: <|$futuresOptionsSymbol|$name|>\n";

# build the contract list
$ua->BuildContractList($futuresOptionsSymbol,0,1);

$ua->{ShowDecimalPoint} = 1;
$ua->{IncludeHolidays} = 0;
$ua->{detrendMethod} = 0;
$ua->{FillInCashPrice} = 0;

# determine the number of contracts
$numberOfContracts=$ua->NumContracts;
print "$numberOfContracts\n";

# fetch the contracts one by one
for($i = 0;$i<=$numberOfContracts;$i++) {
$ua->GetNextContractDetails;
$deliveryCode=$ua->ContractDeliveryCode;
$strikePrice=$ua->ContractStrikePrice;
$firstDate=$ua->ContractFirstDate;
$lastDate=$ua->ContractLastDate;
print "$futuresOptionsSymbol|$deliveryCode|$strikePrice|$firstDate|$lastDate\n";

# create


# create output file for delivery code
$fileName=$futuresOptionsSymbol."_".$deliveryCode."_".$strikePrice;

open(OUTPUT,">$output_directory/$fileName") or die "Can't open $output_directory/$fileName!";;

$CLIP = Win32::Clipboard();

# fetch the time series data for each contract
$numrec_1 = $ua->RetrieveOptionSeries($futuresOptionsSymbol,0,$deliveryCode,$strikePrice,$firstDate,$lastDate);
$numrec_2 = $ua->CopyDataToClipboard(); # the second numrec is the number of returned records which may be different

my $text = $CLIP->Get();
my @data = split("\n",$text);
foreach (@data){
($date,$dow,$dydm,$open,$high,$low,$close,$cash,$vol,$oi,$tvol,$toi,$ClosingBid,$ClosingAsk)= split(',',$_);

# format datetime
$template = 'A4A2A2A*';
($date_yyyy,$date_mm,$date_dd)=unpack($template,$date);
$datetime=$date_yyyy."-".$date_mm."-".$date_dd;

print OUTPUT "$futuresOptionsSymbol|$dydm|$strikePrice|$datetime|$open|$high|$low|$close|$vol|$oi|$tvol|$toi\n";
} # foreach
close(OUTPUT);
$CLIP->Empty();

my $update="$yyyy-$mm-$dd|$name|$futuresOptionsSymbol|$deliveryCode|$strikePrice|$firstDate|$lastDate|$date|$vol|$oi|$tvol|$toi\n";
print UPDATE "$update\n";
} # for

} # while
} # sub
my $output_directory="M://MarketData/Daily/CSI";
print "$output_directory\n";
getFuturesOptions($output_directory);


...WARNING: I am an optimal f'er
Previous Thread :: Next Thread 
Page 1 of 4Goto to page: [1], 2, 3, 4 Next