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.
Grant: After asking my name, you lost me. .. While I would not classify myself as a Christian, my friends call me Mike. My enemies call me a** well, nevermind.... I am not too concerned that I don't follow all you are saying because I see the big picture here and it will be great. I call you hero cause you have giving me an answer I have looked for for months and it was on my box all along. (box= computer) I do not pay for programs or services. All my info is free!! Partly cause no one will layout the info they way I wanna see it and also I believe all information is and should be free. Therefore, the aformentioned program tip is the missing link to my "cabinet". I know most want the holy grail delivered in a fancy box that cost $3995 with a sextuple your money back promise, but I much prefer the old fashioned way. Brain power and creativity. I look forward to being able to throw some powerful info your way. Mike PS for some reason my "signature" does not post...
Mike, I consider myself a Christian but I don't believe in God. Is that a contradiction? I have two fundamental beliefs 1) You get what you pay for - the most expensive may not be the best, but the cheapest certainly isnât. 2) If someone can s**t on you, they will. Grant.