Tag Archives: excel

Kaplan-Meier Curves with R

While Excel (and similar spreadsheet programs) are powerful, they are not really suited for survival analysis. For example, graphing Kaplan-Meier curves with Excel is less than ideal. Survival analysis will often require a statistical software package, like  SAS, SPSS, STATA, XLSTAT, etc. R is a free alternative that is widely used by academics. Be warned, the learning curve for R is steep, but the references below should be helpful.

An R script entitled KM graphs.R is provided that will allow you to graph Kaplan-Meier curves using R. You will have to download and install R (and RStudio if you like). Professor Roger Peng, at Johns Hopkins University, has an entire online course introducing R on YouTube and Coursera. This YouTube video shows you how to install R on Windows; he also has a video to install R on a Mac. You will also need to format the survival data you want to graph in a .CSV file. The file should have three columns: time to event, event occurrence, and treatment. The event occurrence and treatment columns should contain either a 0 or 1 to indicate whether the event has occurred or whether an individual received treatment, respectively. An example file entitled data.CSV is provided.

Once you have installed R, download the data.CSV file to the working directory in R and run the KM graphs.R script. You can then replace the data in the .CSV file with the data you want to graph.


  1. UCLA Institute for Digital Research and Education.
  2. Use Software R to do Survival Analysis and Simulation.
  3. Survival Analysis in R (www.openintro.org).
Tagged , , ,

Option Pain Calculator in Excel

I’ve become somewhat interested in the Max (Option) Pain Theory recently.  In short, it’s a way to predict the price of a stock on a certain date using open option interest.  The theory, which has been around for a while, is based on the fact that stock prices tend to gravitate towards a point where the majority of options will expire worthless.  More detailed explanations can be found at the following Seeking Alpha article.

There are a number of online max option pain calculators: OptionPain, OptionCalc, MaxPain, MaxPa.in, Strike Pegger, etc.  Unfortunately, these online calculators do not have much historical data, so it’s difficult to determine how well the theory predicts stock prices at option expiration.  I’ve created a Max Pain calculator in Excel that allows you to enter historical option interest data to back-test how well the theory works.  For more on how Max Pain is actually calculated, see Optionetics.

From my limited analysis, the theory is somewhat useful for certain stocks.  The stocks should have a ton of open option interest like AAPL.  Not surprisingly, the theory works better as the option expiration date nears, i.e. the forecast tends to be more correct at two weeks vs. four weeks from expiration.  People (e.g. Travis Lewis at AAPLPain) are using variations on the theory to trade stocks and options successfully.

Tagged , , ,

Kaplan-Meier Curves in Excel

Clinical studies often use Kaplan-Meier (aka survival) curves to show the proportion of patients that have survived after a certain period of time.  There are a several articles that show you how to do the math:

Unfortunately, Excel does not include a function to graph Kaplan-Meier curves.  You have to reformat the data to be able to create survival curves in Excel.  Check out SCEW, an Excel add-in that allows you to create Kaplan-Meier curves.  You do not have to download the add-in if you’re willing to manually type in the spreadsheet formulas that reformats the data (see Appendix A).  Once you have reformatted the data, you can use the scatter graph function to create the Kaplan-Meier curve.  For your convenience, I have created an Excel spreadsheet on Scribd that you can use.

Update: I realized that downloading from Scribd can be inconvenient, so I’ve included a link to the Excel file on Google Drive. For the more daring, try graphing Kaplan-Meier curves with R.

Tagged , ,