Is there an easy way to program Excel to tell me what an associated level in say the QQQ is for the Mini-Nas? Thanks in advance
I think that doing it in Excel would be a waste of time. If you just look at the closing prices, you can do the math in your head. For the Qs, $1 = 40.xxx NDX points, and $1 SPY = 9.9xxx S&P points. The exact value drifts over time, so you need to check it periodically. Over the last 12 months, the variation has been plus-or-minus 1.5%, and it can drift as much as plus-or-minus 8% over longer periods. But the arbs keep the ratio from suddenly changing by an amount which would be large enough and last long enough for a retail trader to exploit. As you know, comparing these to their corresponding futures is an entirely different story, depending on the time remaining until expiration, the risk-free interest rate, and the market's mood. That's why books about futures start by explaining things like backwardation, contango, and calculation of fair value.
I havent put anything together in Excel, however I routinely calculate "convergence" levels between the indicies(INDU: SPX: OEX), etc, etc...All you need to do is determine the multiplier...This is obviously a static number in indicies which are almost perfectly correlated...However, in other indicies there is some tracking error, so you will probably need to constantly adjust it...
Thanks guys. I've worked out how to do it in Excel and I need it programmed in there because I'm working off the QQQ chart and then need to convert that into futures and then convert that into my bookies quote, (he takes 3 points) but then no tax.
It would help if you could describe the end result you want to achieve. If you're merely trying to determine how much the underlying is over-/under-priced relative to the futures, I believe that some feeds -- perhaps yours -- provide that directly.
nusrat All I wanted was what the associated level of say QQQ at 25.87 would be on the Nas futures. As one of the members here said, it wasn't too hard to work out. I've got it all nicely programmed in now.