Tag Finance

Posts: 4

Finance and Economics Extension

I'm playing around with the Rapidminer Finance and Economics extension, from Broad Reach Analytics, and I like it.  It's a slick and fast way to get financial data into your processes and there are lots of operators that let you get data from Bloomberg, the World Bank, and Yahoo.

It would normally take me 30 minutes to an hour to cobble together stock quote data from Yahoo into a spreadsheet, then validate the dates, and import them into Rapidminer. Now, using the extension's operator, it took me 5 minutes.

Just for fun, I created this advanced S&P500 chart using color coded VIX values, which took another 5 minutes. Done!


This extension holds great promise but it appears there haven't been any recent updates. Still, one extension to keep an eye on.


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, iVolatility.com, 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}.

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).

  • ROILog > 0 is profit
  • ROILog < 0 is a loss
  • Doubling occurs when ROI_{Log}=ln(2)=69.3%
  • Total loss occurs when ROI_{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 iVolatility.com for the 10, 20, and 30 day HV's. Check!



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.

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.

    Sub ETF_TREND()
    ’ LinReg MacroMacro recorded 3/8/2007 by Thomas Ott
    ’Clear Data
    Selection.ClearContentsCalc ETF Trends
    ActiveCell.FormulaR1C1 =8 WeekRange(H1).Select
    ActiveCell.FormulaR1C1 =13 WeekRange(I1).Select
    ActiveCell.FormulaR1C1 =26 WeekRange(G9).Select
    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
    Range(I27:I54).SelectFormat Columns
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
    Formula1:=0Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater,
    Formula1:=0Selection.FormatConditions(2).Font.ColorIndex = 50
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat =0.000000Selection.NumberFormat =0.00000Selection.NumberFormat =0.0000Selection.NumberFormat =0.000”
    ’ Percent Change Function
    ActiveCell.FormulaR1C1 =% ChangeRange(J2).Select
    ActiveWindow.SmallScroll Down:=18
    ActiveCell.FormulaR1C1 ==(RC[-5]-R[-51]C[-5])/R[-51]C[-5]”
    ActiveWindow.SmallScroll Down:=6
    Selection.Style =PercentSelection.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.

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 ETF’s (or index’s) trend. See our last XLS example.

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.


Financial Asteroids & Their Effects on Trends

Several years ago, when I was living in New Mexico, I had a girlfriend who used read very esoteric books about cutting edge theories on biology, evolution, and astronomy. We were talking about "what-if" scenarios one day and the conversation drifted to some called "punctuated equilibrium." It was explained to me, at the time, that species evolve slowly in an environment that's in equilibrium. A sudden leap in a species evolutionary development happens when a catastrophic internal or external event occurs. The example she used was the asteroid wiping out the dinosaurs theory. The dinosaurs lived and evolved in a relatively state of equilibrium until an asteroid killed them suddenly and allowed mammals to evolve rapidly.

Wikipedia defines it as:

Punctuated equilibrium (also called punctuated equilibria) is a theory in evolutionary biology, which states that most sexually reproducing species will show little change for most of their geological history. When phenotypic evolution occurs, it is localized in rare events of branching speciation (called cladogenesis), and occurs relatively quickly compared to the species' full and stable duration on earth. [via wikipedia]

I understand that some of these theories have changed over the years but its premise stayed with me for years. Can the upset of punctuated equilibrium (PE) or something similar explain the sudden emergence or death of trends?

Although we like to believe in market equilibrium (PE?) and slow evolution of prices when new fundamentals occur, I'm a firm believer that the markets themselves are not always seeking equilibrium. Sentiment and fundamentals drive a trend and then the trend in turn drives the sentiment and fundamentals of that market. Trends become reinforcing and suck more and more capital into them until they crash. Then the crash becomes self reinforcing as the sentiment and fundamentals change and a new trend emerges downward. Where's the equilibrium in that?

What truly interests me in trend following is the moment a financial asteroid hits the trend. What are the events or sudden changes in the financial environment that will allow some trends to die and cause others to evolve? Was it single event or several smaller events together that killed a trend or caused financial havoc? The first example that comes to mind was the Russian domestic debt default in the late 90's that led to Long Term Capital Management (LTCM)'s sudden demise.

I know I don't have all the answers, all I have is an interesting brain tease, and an interesting biological theory that I'm trying to superimpose on existing trends, hoping to uncover future financial asteroids.