VIX - starting point

**ayla** · 12-09-2006, 04:10 AM

Originally Posted by fedgolfer

just posted this in Fab's talk thread with a little more text there, from decisionpoint.com:

"Raw VIX numbers are of limited value.

I think the chart I've just posted is very enlightening but I do agree that without the 20, 50 and 200 day VIX SMA averages, one couldn't really tell alot. Perhaps that is what they mean.

Because the VIX peaks as shown on the chart correspond to PRECISELY almost to the day, downturns, at least with the past couple of years data. I haven't seen precision like that with any indicator I've looked at so far.

I may be getting a little arrogant but I think I will have to say that just because one claims to be an analyst, that doesn't mean they should be believed (that includes me also of course, LOL). The chart speaks for itself IMO.

But if the reference to "limited value" applies to "day trading", then I totally agree. The VIX trend seems to be good for a week or so and during that week, there can be some downturn and you don't really know it until the next day (which is probably what my TSP balance is probably going to reflect this week. I think I should have sold on Friday.)

I just wish I would have known the "next day" back in May when the VIX started its assault to the peak. I still would have had losses but they would have been significantly less.

**fedgolfer** · 12-09-2006, 04:16 AM

... i didn't mean to discount anything you've added. I agree w/ your crossover anlysis. I just thought I'd add that verbage to this thread so other people can see another way to use VIX with bollingers or price channels when swing trading. That's all.

**Fivetears** · 12-09-2006, 03:24 PM

ayla, two Q's regarding your chart VIX2006.xls:
1. Is there a reason why the column F (Volume) values are inconsistantly entered?
2. Is there a reason why the "Date' data fields in columns A & M do not match up beginning on row 201 and ending on row 724?

I really want to learn and understand.

Thanks.

Originally Posted by ayla

Very interesting last couple of days. The VIX is definitely forming some sort of "peak". Can't guarantee that it isn't ominous but I'm thinking it is going to be a 'minor" peak like what formed on January 20, 2006. That would mean any pullback is going to be relatively minor.

When/if the VIX crosses above the 200 day average, I'm pretty sure at that point that I will be moving my funds to the "parking lot" (i.e. the G Fund probably) until signs that the "peak" is over. What those signs are, I'm still trying to learn.

The 20 day average (SMA) is still not above the 50 day average. That has been known to be an important milestone before the VIX data really reflects that the market may be in trouble.

I hate to keep reminding people of how new I am at this but in case anyone new to the forum is reading this, I want them to know that I'm rather inexperienced with predicting market action (and my returns this year aren't that great but I didn't know this stuff until only recently. oh well.) And I have had some difficulty pulling this data out of all the other data I have. I'm still learning Excel as well...

but that said, this data chart IS VERY interesting - it will be obvious to you just from glancing that something is going on - in particular, look at the spikes forming in the bottom right hand corner.

**ayla** · 12-10-2006, 02:36 AM

Originally Posted by Fivetears

ayla, two Q's regarding your chart VIX2006.xls:
1. Is there a reason why the column F (Volume) values are inconsistantly entered?
2. Is there a reason why the "Date' data fields in columns A & M do not match up beginning on row 201 and ending on row 724?

I really want to learn and understand.

Thanks.

I have rechecked the data and it still looks good to me though I totally understand why you are asking the questions. I should have explained this initially.

1. - Column F is an exact replica of what I downloaded from Yahoo for ^VIX. I can't answer why they are inconsistent. I remember having the same question myself. But I left it in because if you want to update the data, it is a simple cut and past from: http://finance.yahoo.com/q/hp?s=%5EVIX

2. Reasons columns A & M don't line up:

Columns A thru L is actually a different database (i.e. Yahoo) than columns M, R, S, T, and U. Getting these matched up using the common variable of "date" is the reason Column M and Column A aren't lined up. Since Number of dates in TSP datebase is less than number of dates in YAHOO VIX database.

It might have been clearer for you to see this if I would have just provided two data sheets instead of putting the data side by side. I may do that in future uploads.

Note column Q is labeled "VIX - date Matched", not just 'VIX" because it has been extracted from column G only when the date in column M for the same row is found in Column A.

More details:
Col A in VIX data downloaded from YAHOO includes extra dates not included in the TSP date column M. This mismatch causes some extra effort to be made (which I should have explained). Data posted in columns N,O,P and Q are extracted from columns G, I J and K after successful match in the YAHOO date column. The value is extracted (using an Excel "index" formula) and posted.

For example, the first date in Column M (M201) which is Nov 5, 2004 has a value for the VIX that is extracted from the value in Column E (E 205). Can't just pull out the VIX value from the same row since VIX dates don't necessarily line up with TSP dates not just because of my sloppiness but the databases of dates are not the same.
I could have been less sloppy and at least started the dates out correctly side by side but Column M will always eventually "fall behind" column A in this example because there are holidays that cause dates to be excluded from the TSP date database that ARE included in the YAHOO VIX date database.
Bottom line is that the chart is ONLY made using entries from Column M thru U. As long as this data and the corresponding date is correct, that is the most important.
BUT.. the reason the other data is there is that the data in Columns A thru N is the raw data from Yahoo that is needed to eventually end up in Columns N thru Q.
Columns M, R, S, T and U are extracted from the TSP share price chart (and sorted in ascending mode) from: http://www.tsp.gov/rates/share-prices.html

I did some spot checking in case I made some errors (like I have definitely made before) and it looks good. I picked a date from column N, and looked at it from the TSP share database and compared it to the IFUND in column U and they matched. I also made sure the VIX number in column Q for a TSP date (in column M) matches a VIX number in column G for the identical VIX date (in column A).

Probably clear as mud. Sorry, I've answered this by repeating a lot. I may try to create two data sheets as explained above but in the meantime, only look at columns M thru U. That is what the chart uses. But if you ever want to update the chart, you MUST update columns A thru N.

Feel free to check columns M, R, S, T, & U and let me know if any rows have errors as compared with the TSP share database.
Also, let me know if you see erros from columns A thru N as compared with the YAHOO VIX database. But please don't worry if columns M,R,S,T, & U do not line up row by row with columns A thru N. (Notice the length of the TSP date is shorter than the length of the VIX date column A - and will almost always be shorter if date interval covers any government holidays.)

**ayla** · 12-10-2006, 04:33 AM

Originally Posted by ayla

It might have been clearer for you to see this if I would have just provided two data sheets instead of putting the data side by side. I may do that in future uploads.

Attached is a zip file of the new data where yahoo data is separated from TSP data on a separate work sheet. (Had to use zip because size exceeded limits.) You will notice the number of dates in the date columns is not the same for Yahoo and TSP as I explained previously so that hasn't changed.

I filled in Nov1 thru Nov 4 2004 data for the TSP data just to make it look better and be less sloppy but the chart is the same and the results are the same as I posted before with the VIX spiking in the lower right hand corner. But this set of data has more "clarity", I hope others agree.

This set of data should have the formulas that I discovered missing in my earlier Excel data which I'm sure made it more confusing (only values were posted, not the equations that resulted in the values). You will now see the "index" formulas on the TSP data sheet. You therefore can see clearly the primary way I "match up" the TSP dates with the Yahoo dates.

The chart is drawn exclusively from the tsp data worksheet because this set of dates is the "least common denominator".

In order to upload and meet size limit requirements, I had to eliminate some of the Yahoo data columns which aren't necessary for the chart but which did make it easier if anyone else wanted to update this chart with future Yahoo VIX data by simply cutting and pasting. Still can be done but more complicated. Oh well. I also deleted data for F, C and S funds but I fund prices are still included.