Mr Spread,
Whatâs your Christian name? âMrâ is somewhat formal.
This is not a big deal in the scheme of things but itâs a problem Iâve been meaning to address for ages but avoided. Now I have resolved the problem. Itâs another example of what can be achieved in Excel.
I record the DAX, STOXX, and SMI futures, 1-minute high-low every day. This gives me the underlying price and time for their respective options.
Hitherto, to extract the prices this would be the sequence of events:
Export the data in .txt format from the quote source (Visual charts).
From Excel, File, Open, DAX,txt, STOXX.txt and SMI.txt.
As these are .txt files Text Import Wizard opens with the raw/unformatted data visible.
To retrieve the data in the required format, I click Next, tick the Comma Delimiter box.
Click Next, Ticker (shows underlying code), Do Not Import Column (skip).
Repeat for columns Per (donât know what that refres to), Date, Open, Close, Vol(ume), Openint(erest).
This leaves me with the columns Time, High, Low (I use high/low as a compromise â I donât know if an option traded at the high, low, open or close in a 1-minute period. Therefore, the I add high to low, divide by 2 and use that as a reasonable guess.)
Then I press Finish.
The result is a typical spreadsheet with Time, High and Low. Next I enter =(high+low)/2 on each line and Copy, Paste to the last value.
Next, I format the values to zero decimal places (just looks tidier).
Next I open another spreadsheet recording the times and values. Iâm interested in options with up to 8 expiry months (to December 08). From each .txt sheet I Copy and Paste the latest values.
The whole sequence is repeated for each underlying.
To do this manually every day is a real pain in the arse. Now Iâve solved it, and Iâm ecstatic (simple things, simple minds).
(Due to the illiquidity of back months (Mar 07 through to Dec 08), reliable price data is elusive, so I calculate these values from the front-month via the cost-of-carry model (courtesy of ProfitTaker of this forum).
Using the front-month rather than cash (as originally suggested) for fair value is preferable as any premium/discount on the front-month future should, I suspect, be reflected in the back months (but this may be debatable); cash carries no premium/discount. I calculate back a theoretical cash price and extrapolate from this the back-month values.
The sum total is 24 separate underlying values at 1 minute intervals for the trading day.
Theoretically, prices can be downloaded automatically at specific intervals, eg 1-minute, from the source into the spreadsheet but so far this has not worked, for various reasons.)
Iâm open to commissions.
Perseverance is the key here, Mr Spread. Or maybe I should get a better quote system.
Grant.
Whatâs your Christian name? âMrâ is somewhat formal.
This is not a big deal in the scheme of things but itâs a problem Iâve been meaning to address for ages but avoided. Now I have resolved the problem. Itâs another example of what can be achieved in Excel.
I record the DAX, STOXX, and SMI futures, 1-minute high-low every day. This gives me the underlying price and time for their respective options.
Hitherto, to extract the prices this would be the sequence of events:
Export the data in .txt format from the quote source (Visual charts).
From Excel, File, Open, DAX,txt, STOXX.txt and SMI.txt.
As these are .txt files Text Import Wizard opens with the raw/unformatted data visible.
To retrieve the data in the required format, I click Next, tick the Comma Delimiter box.
Click Next, Ticker (shows underlying code), Do Not Import Column (skip).
Repeat for columns Per (donât know what that refres to), Date, Open, Close, Vol(ume), Openint(erest).
This leaves me with the columns Time, High, Low (I use high/low as a compromise â I donât know if an option traded at the high, low, open or close in a 1-minute period. Therefore, the I add high to low, divide by 2 and use that as a reasonable guess.)
Then I press Finish.
The result is a typical spreadsheet with Time, High and Low. Next I enter =(high+low)/2 on each line and Copy, Paste to the last value.
Next, I format the values to zero decimal places (just looks tidier).
Next I open another spreadsheet recording the times and values. Iâm interested in options with up to 8 expiry months (to December 08). From each .txt sheet I Copy and Paste the latest values.
The whole sequence is repeated for each underlying.
To do this manually every day is a real pain in the arse. Now Iâve solved it, and Iâm ecstatic (simple things, simple minds).
(Due to the illiquidity of back months (Mar 07 through to Dec 08), reliable price data is elusive, so I calculate these values from the front-month via the cost-of-carry model (courtesy of ProfitTaker of this forum).
Using the front-month rather than cash (as originally suggested) for fair value is preferable as any premium/discount on the front-month future should, I suspect, be reflected in the back months (but this may be debatable); cash carries no premium/discount. I calculate back a theoretical cash price and extrapolate from this the back-month values.
The sum total is 24 separate underlying values at 1 minute intervals for the trading day.
Theoretically, prices can be downloaded automatically at specific intervals, eg 1-minute, from the source into the spreadsheet but so far this has not worked, for various reasons.)
Iâm open to commissions.
Perseverance is the key here, Mr Spread. Or maybe I should get a better quote system.
Grant.
