Calculating Historical Volatility

 Hi there!   This is one my most popular posts and I would love it if you became an RSS Reader!

The inspiration for my S&P500 Volatility Timing model came from rereading portions of Mandelbrot’s book, The (Mis)Behavior of Markets, and trolling around the Internet for Nassim Taleb’s research work on risk. I think both guys push the envelope on truly understanding unseen risk and those pesky financial asteroids. Since my model is currently being developed, I thought it would be worth my while to truly learn and understand how historical volatility (HV) is calculated. I first searched the Internet for any free data downloads of HV but came across several pay for data download sites.

One of them,, seemed to have comprehensive data but it was expensive. One year’s worth of HV for one asset price would’ve cost me $5! So what does any engineering type person do in the face of expensive solutions? He (or she) build’s their own cheaper solution. I decided to calculate HV on my own after reading about it on wikipedia. Now, I’m submitting this analysis for peer review as I’m treading in unfamiliar waters. Please feel free to correct me if my computations or understanding of the material is wrong.

Wikipedia defines HV as:

The annualized volatility σ is the standard deviation σ of the instrument’s logarithmic returns in a year.


The generalized volatility σT for time horizon T in years is expressed as:

sigma_T = sigma sqrt{T}sigma_T = sigma sqrt{T}.

Note: There’s a flaw in the Wikipedia’s formula logic after the generalized volatility formula above as pointed out by C++ Trader (thanks for the catch). Please see the related links below for more information on the correct calculation of HV.

Note that the formula used to annualize returns is not deterministic, but is an extrapolation valid for a random walk process whose steps have finite variance.

So the first step is to calculate the S&P500′s logarithmic returns for a year. I’ll be using the weekly time series and I’ll analyze it in a handy Excel spreadsheet here: HV Example.xls

Once again I’ll turn to wikipedia for an explanation of logarithmic returns:

Academics use in their research natural log return called logarithmic return or continuously compounded return. The continuously compounded return is asymmetric thus clearly indicating that positive and negative percent returns are not equal. A 10% return results in 9.53% continuously compounded return while a -10% results in -10.53%. This clearly indicates that the investment will result in a dollar amount loss corresponding to the difference between the absolute values of the two numbers: 1% (this is an approximate equality).

  • Vi is the initial investment value
  • Vf is the final investment value

ROI_{Log} = lnleft(frac{V_f}{V_i}right)ROI_{Log} = lnleft(frac{V_f}{V_i}right).

  • ROILog > 0 is profit
  • ROILog < 0 is a loss
  • Doubling occurs when ROI_{Log}=ln(2)=69.3%ROI_{Log}=ln(2)=69.3%
  • Total loss occurs when ROI_{Log}to-inftyROI_{Log}to-infty.

This should be straightforward and I will calculate the weekly ROI for the S&P500. Why? Well I’m interested in calculating weekly HV so my Vi will be the week(1)’s closing price and Vf will be week(2)’s closing price. For the next iteration Vi will be the week(2)’s closing price and Vf will be week(3)’s closing price and so forth.

Next I created an Excel Macro that would calculate the natural log and simultaneously calculate the HV for 10, 20, and 30 days using the standard deviation of the daily logarithmic returns multiplied by 252 (see related links below).

There you have it, your very own weekly HV! Feel free to download the Excel macro and play with it. By all means, please critique my analysis and let me know if my logic is flawed! The more I learn about this, the more my ATS takes shape!

Update: The Excel Macro matches the output from for the 10, 20, and 30 day HVs. Check!



Building an AI financial market model – Lesson V

The Downloads For This Lessons Have Been Updated for RapidMiner4.0

In this lesson we will build a prediction experiment for you to test new data against and predict if Gold’s trend is UP or DOWN! Since I put you through four grueling lesson previously, I’m going to take it easy on you here and give you the completed model.

A prediction model, for trend analysis, is typically the same for every one you develop. All it really requires is a data loader for your test data, an experiment visualizer (this is not mandatory but highly suggested), a model loader, and finally a model applier. That’s it, only 4 operators are needed to complete your entire experiment!

Step 1 – Build the Prediction Experiment

This is where you get off easy, I’ve built the prediction model already for you. All you have to do is download the predictive experiment (in zip format: GA-Gold-prediction) and then load it into YALE. (click here for the RapidMiner Compatible Prediction experiment)

Step 2 – Load in the Test Data & Model

Get your test data and load into YALE through the ExcelExampleSource operator. For this example we will use the following Excel spreadsheet: GA-Gold-Test. Select this spreadsheet in the operator and make sure the label field is set at zero (your test data should not have your output label in it because this is what you are trying to predict, it should therefore be zero) and change the id field to 1 (this is for your date column).

Next load in your model (gold_final.mod) that you created in Lesson IV and that I so graciously added to the GA-Gold-Prediction zip file. (click here for the RapidMiner Compatible version)

Step 3 – Run the Experiment

Click Run and YALE will spit back the results in a few seconds. Click on the data view in the Results Tab and you’ll see your predicted trend value (UP or DOWN). Congratulations! You’ve built your first trend model and predicted the trend in Gold!


I hope that you enjoyed these lessons and learned a little bit about the powerful ability of YALE. When I started learning YALE, no one was there to help me. It was one heck of learning curve but after trail and error, it got significantly easier for me to create models. I would guess that it took me 9 months of learning to get to where I am now, it took you 5 lessons!

If you decide to continue with building models, I highly suggest that you continue using YALE, you’ll be surprised at what it can do. Make sure you tip me when you make that cool million trading Gold.

Future Lessons

In future lessons I hope to show my readers how to build a model driven by genetic feature selection and an event driven model. If you have any questions or comments, please email me or drop me a comment. If you enjoyed these lessons, please consider subscribing to my RSS feed and passing the word about Neural Market Trends around.

Please note, the information I’m sharing with you is very valuable and FREE, there’s nothing like this available on the Internet that I know of.  Please consider becoming an RSS Reader or buying something from my sponsors.


Build an ETF Trend System in Excel

Today I wanted to share with you a part of the algorithmic back end to my ETF Trend System. Note, I said part, I’m not giving away all my secrets. It’s written completely in Excel, incredibly simple, and is a macro that you can import. The system works by using something called linear regression slope.

What’s that?

The easiest way to understand what linear regression slope is, is to think back to your basic statistics class. Linear regression is the best fit” line between a bunch of data points. A line is defined by the formula: y = mx+b, where y is your data point’s position on the y-axis, m is the slope, x is your data point’s position on the x-axis, and b is the slope intercept. What this ETF Trend following system does is place a best fit” line across several price data points (8, 13, and 26 weeks) and then calculate the slope of the line. If the slope is positive, you have an upward trending ETF. Conversely, if the slope is negative then you have a downward tending slope.

As the ETF trades in the markets, the price goes up, down, and sometimes consolidates inside a trend. When that happens the linear regression slope begins to flatten” out, meaning the slope becomes more horizontal. When combined with two or more periods, like an 8, 13, and 26 week period, you can see the overall short-term, medium-term, and long-term trends in a particular ETF. This makes for a great indicator that warns you of either a change in trend or a dip buying opportunity.

Ready to try it out for yourself? Just follow these easy steps and you’ll be ETF Trend following in no time. First you have to make sure you have Excel 2003 or a later version installed and access to ETF data.

Step 1: Get two years of ETF data. You’ll need your favorite ETF and two years of weekly closing data. Make sure you include the date, open, high, low, and closing prices. You can cheat, and follow along with my example by downloading this XLS: GSPC ETF Trend Example

In the example contained in this lesson, I use the S&P500 weekly data but you can substitute that with any ETF or index you’d like to follow

Step 2: Copy the macro code below and paste it into your Excel Visual Basic Editor. You can find this editor by going to Tools > Macros > Visual Basic Editor.


′ LinReg Macro
′ Macro recorded 3/8/2007 by Thomas Ott

Clear Data
Calc ETF Trends
ActiveCell.FormulaR1C1 = 8 Week”
ActiveCell.FormulaR1C1 = 13 Week”
ActiveCell.FormulaR1C1 = 26 Week”
ActiveCell.FormulaR1C1 = =SLOPE(R[-7]C[-2]:RC[-2],R[-7]C[-6]:RC[-6])”
Selection.AutoFill Destination:=Range(“G9:G54”), Type:=xlFillDefault
ActiveCell.FormulaR1C1 = =SLOPE(R[-12]C[-3]:RC[-3],R[-12]C[-7]:RC[-7])”
Selection.AutoFill Destination:=Range(“H14:H54”), Type:=xlFillDefault
ActiveCell.FormulaR1C1 = =SLOPE(R[-25]C[-4]:RC[-4],R[-25]C[-8]:RC[-8])”
Selection.AutoFill Destination:=Range(“I27:I54”), Type:=xlFillDefault
Format Columns
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater,

Selection.FormatConditions(2).Font.ColorIndex = 50
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = 0.000000”
Selection.NumberFormat = 0.00000”
Selection.NumberFormat = 0.0000”
Selection.NumberFormat = 0.000”
Percent Change Function
ActiveCell.FormulaR1C1 = % Change”
ActiveWindow.SmallScroll Down:=18
ActiveCell.FormulaR1C1 = =(RC[-5]-R[-51]C[-5])/R[-51]C[-5]”
ActiveWindow.SmallScroll Down:=6
Selection.Style = Percent”
Selection.NumberFormat = 0.0%”
Selection.NumberFormat = 0.00%”
Selection.AutoFill Destination:=Range(“J53:J54”), Type:=xlFillDefault
End Sub

Step 3: Save the file and then activate the macro by clicking Run. You should see that the macro created four new columns and color coded the slopes. It should look something like this XLS: GSPC ETF Trend Example 2

Step 4: This step is optional but I highly recommend you do this. You should build a chart from that 8, 13, and 26 week slopes. This will help you identify the peaks and valleys in the ETFs (or index’s) trend. See our last XLS example: GSPC ETF Trend Example 3

There you have it! A very simple and fun way for you to build a basic ETF trend system. Please feel free to modify the macro, or add to it as you see fit. If you have any questions or comments, please feel free to contact me.


Build Your Blog Traffic Using Excel and Data Mining

This past Saturday, I posted about using data mining to look for patterns in your blog traffic. I wrote that you can use something called an Excel Pivot Chart report to get a better feel for how your readers are interacting with your site. What I should’ve written was that you can use an Excel Pivot Table report, the chart is optional. So why should you build an Excel Pivot Table?

Building a report is a great way to see trends in your readership and its easy to do. Once you see things happening on your site you can start asking questions like, what type of content drives the most traffic and on what days?” The Excel Pivot Table report won’t be able to answer that question but it can answer the question, what’s my busiest day, or what’s my optimal post per day quantity, and what’s my most popular category.”

Interested? Here’s how you do it in 5 easy steps.

Step 1 – Gather Data

If you use Google Adwords, or another site statisitc monitor, download your visitor data. You can choose what ever time frame you like, a good rule of thumb is about 2 months worth of data. You’ll need to get the number of hits and the date of the hits. Next, add this information to an Excel spreadsheet and add the following columns: Weekday, Number of Posts, and Category.

Step 2 – Transform the Data

Go back between the dates of your data download and fill in the columns for Weekday, make sure to match the date with the right weekday. Next, fill in the Number of Posts column with, you guessed it, the number of posts you did that day.

Step 3 – Create a Category

When you get to the step of data mining your traffic, you’ll want to know what content drives your traffic and on what days. Adding a key of categories will help you accomplish that. I entered the number 1” if the post that day was about Forex, 2″ if it was for stocks, etc. You get the point. If you posted more than one post on any given day and it was about more than one topic, you can add a second or third category column. You get as detailed as you want, its really up to you.

Step 4 – Build the Excel Pivot Table

Once you have all your information, you can build the table. Go to Data > Pivot Table and follow the instructions. You can place the table in your existing worksheet or a new one (I usually go for a new worksheet). Select your data range to include the Weekday, # of Visitors, Posts Per Day, and Category.

Once you did that you’ll see your new worksheet with a little floating menu system. You can drag and drop the fields into your new table. Drag the # of Visitors into the Drop Data Items area, drag the Weekday field into the Drop Column Fields area, drag Posts per Day to the Drop Row Fields area, and lastly drag the Category field to the Drop Page Fields area.

Step 5 – Format the Table

Use Excel’s auto format function in the Pivot Table wizard to select the style of table you’d like to see. When your all done, your spreadsheet should look something like this Blog Data example.

The first step before Data Mining your blog traffic is done! You can easily see what your busiest day of the week is, what’s a good # of posts per day (this is great from an efficiency standpoint), and what’s your most popular category. Just doing this simple Excel exercise can help you identify the ways to build more traffic to your website, which could yield financial benefits if you’re using Adsense or some other monetizing method.

As always, if you have a question please leave me a comment.