Skip to main content

Using C# to Automate Linear Regression in Excel

Excel has a nice linear regression tool as part of the included Analysis ToolPak add-in.  It is quick and easy to use even for the layperson.
To make sure you have this add-in enabled:
  • Go to File > Options > Add-ins
  • Select Manage “Excel Add-ins”
  • Click on “Go”
  • Make sure “Analysis ToolPak” is checked
Enable Addin
Let’s say we have the following sample data of gas and crude oil prices.
Excel Sample Data
And we want to analyze the relationship crude oil pricing has to gas pricing.  To manually run the regression tool:
  • Open the “Data” ribbon menu
  • Cick “Data Analysis”
  • Select “Regression” in the list
  • Click “Ok”
Here are some sample settings for the tool:
Linear Regression Settings
This would be the output:


There are many articles out there on how to interpret and use the results of linear regression analysis.  You may want to take note of the R Square value, which indicates how closely your values relate.

Now let’s say you wanted to run a lot of linear regressions or wanted to re-run them every time there is a new data point.  And you are lazy and don’t want to do that all manually every time.  Time to automate.  The following C# code generates the the same spreadsheet as shown above in the manual example.



It is hard to find resources on how to run add-in tools via Excel interop.  Note the following two statements.
This command is used to activate the Analysis ToolPak add-in:
Code Sample 2
This statement executes the regression analysis:
Code Sample 3
The parameters are the equivalent of the same manual settings shown earlier:Linear Regression Settings
Let me know if this is helpful.

Comments

Popular posts from this blog

Fibonacci Retracement + Donchian Channel Combo Indicator for QuantConnect

So, I have been playing around with QuantConnect, which provides a convenient framework to automate trading strategies using C#.  It has a lot of useful indicators out of the box.  However, one indicator that’s missing is Fibonacci Retracement.  That is understandable since it is generally used as a static manually drawn charting feature.  But I wanted some way to utilize it as an automatic indicator.  That is where Donchian Channels come in. Fibonacci Retracement is a range of values drawn between a high and low.  Donchian Channels provide continuously updating high and low values.  Combining the two concepts yield an automatically updating Fibonacci Retracement.

Let’s dig into Fibonacci Retracements a little deeper.  They are a range of values between a high and low at 61.8%, 50%, 38.2%, and 23.6%.  These values are based on the famous Fibonacci Sequence, where each value is the sum of the two prior values (0, 1, 1, 2, 3, 5, 8, 13, 21, etc.).  Starting at 2, a value divided by the …

Using Paint.NET to Blend Images

Note: this is a repost from my original blog back in 2009. 
Sometimes I need to do a little graphics work, but I do not have Photoshop.  Instead I use a great free tool called Paint.NET.  It is an open source image editing project built with C#.  It has a very rich feature set in many ways similar to Photoshop.  Community plugins are also available.Like other graphics tools, sometimes you have to figure out the tricks to get things done.  In this case I will be showing one way to seamlessly blend the edges of two photos.  I needed to do this for some scrolling banner images, but there are many other uses for this technique.First you want to open both images.  Then expand the canvas on the first image in order to fit the second image.  This is done by selecting the “Image” menu item, and then the “Canvas Size” sub item.Now copy the second image and paste it onto the second image into a new layer.  You can use the “Paste in to New Layer” option under the “Edit” menu.  Move the edge of …