Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Autocorrelation Function

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

i am trying to calculate the autocorrelation function of returns of a single stock.

any suggestions? ive calculated the daily returns but am stumped on what to do next. ive been messing with the CORREL function in excel but i havent been getting very far.

any comments would be appreciated!

View Answers     

Similar Excel Tutorials

How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
MODE() - Find Most Repeated Value in a List in Excel
The MODE() function in Excel allows you to quickly find the most repeated or frequently occuring value within a lis ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...
Dynamic Formulas that Update When you Add Data in Excel
I'll show you how to make formulas and functions that automatically update when more data is added to a range in E ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Get Text from Comments in Excel Including the Author of the Comment - UDF
- Output all text from a cell comment, including comment author, with this UDF in Excel. This UDF (user defined function)
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Output the Worksheet Name in a Cell in Excel - UDF
- UDF to return the name of the worksheet in Excel on which this function is located. There are no arguments for this fun

Similar Topics

I've got a column of data in excel. Say A1:A10. If I want to find the lag1 autocorrelation of this data my understanding is that I can use:


I'd like to use this function (or an alternative) in VBA to find the autocorrelation within an array of data. What sort of notation should I use?

Hi everyone,

I was just wondering if anyone out there has in the past written a user function to calculate the autocorrelation of a series of data, with varying lags. I have been trying to get somewhere with this, but it remains beyond me.




Does anyone know how to calculate an autocorrelation function in Excel?!


I'm trying to understand a correl function that is on a spreadsheet that I inherited. It has the following function: =CORREL(A2:A6,B2:B6)^2 and the answer returns 0.547511 but if I remove the ^2 the answer returns -0.73994. I could be wrong but I'm thinking the ^2 is meaning to raise to the second power....why would they put that in the formula or is my meaning wrong? Thanks in advance for any explaination.

Hey guys.
I have a disaster of an excel assignment on VBA.
One of the questions i am stuck on is as follows:
W r i te a VBA function named SimReturn(low, high, n) that returns an average simulated stock return. You randomly generate the first and the last stock prices (i.e. 2 stock prices for each iteration) between the given low and high values, and calculate an arithmetic stock return. The process repeats for n times. Finally, the average return is calculated based on the n simulated returns. Note that if the low is negative, assign 0 to low in your code. Also, note that you cannot use the Randbetween(i, j) function in your code because that function only produces integer numbers whereas stock prices could take any value (i.e. non-integer) within the range. (Hint: Read VBA function Rnd() in Help)
we have been told that the low is to be 2 high is 4 and n is 200.

any help would be great!!



I have calculated stock returns, but now I want to calculate the portfolio returns with the same stocks. In the attachment I have added my stocks and returns.

I want to invest in one stock at a time. Buying a stock should only happen at moments that my excel sheet has a return in the same row as an open. I want to buy stocks when the returns are positive but also when they are negative.
When a stock is bought, I want to sell it at the first close. Then, another (or the same) stock can be bought.

Can anyone help me with the calculation of portfolio returns?


I have calculated stock returns, but now I want to calculate the portfolio returns with the same stocks. In the attachment I have added my stocks and returns.

I want to invest in one stock at a time. Buying a stock should only happen at moments that my excel sheet has a return in the same row as an open. I want to buy stocks when the returns are positive but also when they are negative.
When a stock is bought, I want to sell it at the first close. Then, another (or the same) stock can be bought.

Can anyone help me with the calculation of portfolio returns?


For my finance course i needed to gather a bunch of stock data for 5 stocks over 7 years (monthly). Then i had to calculate the monthly returns for each stock, I did all that and have the returns etc.

Now I need to create a frequency diagram for the returns using the historical data. Our instructor told us to use the FREQUENCY function in excel, i'm not too familiar with how this works but my guess is that it measures how many values fall between 2 given values. So if i had 3 returns of 11, 12, 13, then the frequency of those falling between 5 and 15 would be 3?

Any help would be appreciated.

Hey everyone I am new to programing in Excel and I do not know enough to wright a function that finds variance of a portfolio. This is what i have so far its messy and does not work.

Function VARPORT(Sa,Wa,Sb,Wb,Sc,Wc,Sd,Wd,Se,We)



VAPORT=((Wa^2)*va)+((Wb^2)*vb)+((Wc^2)*vc)+((Wd^2)*vd)+((We^2)*ve)+(2*Wa*Wb*cab)+(2*Wa*Wc*cac)+(2*Wa *Wd*cad)+(2*Wa*We*cae)+(2*Wb*Wc*cbc)+(2*Wb*Wd*cbd)+(2*Wb*We*cbe)+(2*Wc*Wd*ccd)+(2*Wc*We*cce)+(2*Wd*W e*cde)
End Function

The S stands for a list of stock returns.
The W is what percentage of the portfolio is spent on that stock.
The Covar and Var functions are to calculate the variance of each stock and the covariance between the five.
The three letters are C for covariance and then what stock will be looked at.

This is very confusing to me and I know I just made a big mess, can anyone help or point me int he right direction.

Hi everyone,

I know I can use the correl function to determine correlation between two sets of data, however, I am trying to create a user function that does quickly when the user enters a row number.

Here is the code I have so far:

Public Function corr(n, m) As Integer

Dim i As Integer, j As Integer

startrow = 10 'set this according to the first row of the dataseries
i = startrow + (n - 1)
j = startrow + (m - 1)

corr = WorksheetFunction.Correl(Range("A" & i & ":AB & i), Range("A" & j & ":AB" & j))

End Function

So, when I type in =corr(2,5), I would like my user function to return the correlation of the data series in rows 10+1 and 10+4 - and columns A to AB, which are hard-coded.

So far though, the function I have written returns a #value error.

I would be very grateful if anyone could help me.

Thank you very much,


Dear Excelforum

I am about to creat momentum porfolios and need help to sum daily returns for each stock in the months t-12 to t-1. For example the cell january 01 2009 should sum the returns for the first trading day january 2008 to the last trading day novemer 2008, thereafter I want to roll this function so, do the same for the next month, first trading day february 2009 should sum the returns for first trading day february 2008 to last trading day december 2008.

I have tried with an if formula but I am not sure how to set the constraints.

Please see attached for example data.

I am very grateful for your help!



I have a spreadsheet full of daily stock returns and want to find out which distribution is closest to the one exhibited by the returns. Excel should give me the answer like: this is a normal distribution, or this is a log-normal distribution, or this a Bernoulli distribution....
Is there any function that lets Excel test all distributions and returns the closest or do I have to test my daily returns with all distributions I can think of and see myself if the returns are close to one distribution?

In a second step I want to use the distribution to generate random numbers based on the distribution (like a monte carlo simulation) to simulate scenarios. How can I do this then?

(I have this Excel Add-In called PopTools but I am not so familiar with it)

Would be great if someone had an answer for me!


Hello Friends!!

I want to ask if it is possible to make the CORREL function to calculate custom periods of data. For example CORREL(E52:E181,F52:F181) calculates correlation for 129 rows of data (181-52). Do you think that the range that the function takes into account can be instead represented as the number of the current cell minus a cell N rows above (e.g. CORREL(current cell’s address in column E – N rows:current cell’s address in column E, current cell’s address in column F – N rows:current cell’s address in column F)) and the N will be set in a separate cell? For example for N=10 the function will look as follows CORREL(E171:E181,F171:F181) and CORREL(E151:E181,F151:F181) when the N is set to 30.

Hoping for an answer))


hi !!
can anyone help me with the stuff ?
i have downloaded a file of excel for my assignment work to calculate returns.

There are certain things which i want help in.
I don't know how to extract the returns from the date and other info from a single cell.
for eg.:


these are the data which provide daily retuns of world solar index.
1.i want to copy the dates in one cell and returns in another.

2. i want to convert the daily returns into monthly returns.

the file is attached in the post.
i would appreciate your help.


I'm trying to set up an array to capture stock prices, and then calculate the variance on the array. I've seen this done in the past where the array holds just a single company, but right now my array holds many. Can I reference just 1 column when doing a worksheet function?

My code sample is this

Sub TestScript() 
    Dim returns() 
    m = WorksheetFunction.Count(Range(Worksheets("Prices").Range("A1"), Worksheets("Prices").Range("A1").End(xlDown)) 
    For i = 0 To 10 
        For k = 0 To m 
            returns(k, i) = Worksheets("Prices").Range("A1").Offset(k, i).Value 
        Next k 
    Next i 
    Variance = WorksheetFunction.Var(returns) 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Right now the function would reference the entire array, where I would prefer it to step through the columns within the array. Let me know if this is possible!


I have a series of daily returns e.g.

I intend to look at the cumulative effect of these returns, meaning if I start with an index value of 100 and keep adding the effects of these returns to the previous index value, I'll end up with 100.96 or 0.96%.

I was wondering if there was a way where I could do this in a single formula given the series of returns, rather than having the index etc. calculated.

I searched for the answer to this question here in the forums and wasn't able to find it, so please forgive me if it's here somewhere and I missed it...

I recently used XIRR to calculate my annual stock market returns for each of the last five years (running the function separately for each year), and then tried to calculate the five-year compounded rate of return by entering the continual string of five years of data (i.e., beginning balance plus dates and amounts of additions and subtractions to the account) into a single XIRR function.

On a yearly basis, the returns come out as follows:

2005: +45.2%
2006: +24.1%
2007: -14.4%
2008: +10.6%
2009: +78.9%

Entering the data as a five-year continuum shows a compounded rate of return of 32.8%/year. I interpret this as meaning that if I started with $100 on January 1, 2005, at the end of the five years I would have:

$100 x 1.328 x 1.328 x 1.328 x 1.328 x 1.328 = $413.04

However, if I do the same thing using the separate yearly returns, I get:

$100 x 1.452 x 1.241 x .856 x 1.106 x 1.789 = $305.20

Can anyone please explain to me why there's such a huge discrepancy?


I've written a function that works similiarly to the Excel FIND function. Now, if FIND cannot find the first string inside the second it returns a #VALUE error. How can I make my function do the same?

I am aware of the xlErrValue constant, which returns the error number corresponding to #VALUE (which is 2015) but when I use my function in a cell and it returns this value I literally get 2015 displaying in the cell and not #VALUE as I want.

What extra trick is required to make Excel realise that a function is returning an error value, not a numeric value?

I would like to create a UDF to calculate the maximum drawdown given a
series of returns. I am essentially trying to mimic the second column

-2.54% -2.54%
-0.07% -2.60%
0.12% -2.49%
0.11% -2.38%
-1.58% -3.92%
-4.81% -8.54%
-4.06% -12.25%
-0.56% -12.74%
0.32% -12.46%
-0.31% -12.73%
-1.38% -13.94%
-1.37% -15.12%
-0.43% -15.49%
-0.10% -15.57%
0.90% -14.81%
4.47% -11.00%

If the upper left hand cell is A1 (returns entered into column A), then

B1 has the formula
Cell B2 (and all the other ones with cell references as appropriate)
have the formula

The maximum drawdown can be obtained by taking the min of the second
column (-15.57%).

Here's my code -- I keep getting a #VALUE! reference. I am trying to
replicate the end result without all the intermediate steps.

Function Drawdown(Returns As Range) As Variant
Dim Cumulative() As Variant
Dim i As Integer
' Starts with Cumulative Returns as 0 if .
If Returns(0) > 0 Then
Cumulative(0) = 0
Cumulative(0) = Returns(0)
End If
For i = 1 To (UBound(Returns()))
If (1 + Cumulative(i - 1)) * (1 + Returns(i)) > 1 Then
Cumulative(i) = 0
Cumulative(i) = (1 + Cumulative(i - 1)) * (1 + Returns(i)) - 1
End If
Next i
Drawdown = Application.WorksheetFunction.Min(Cumulative)

End Function

Any help is welcome!

I have a summary spreadsheet, "April Daily Revenue Impact.xls" that has a tab
for each day of the month. Also, for each day of the month there is a
seperate spreadsheet that contains the orders for each day. The tabs on the
summary spreadsheet should look at the daily order log and summarize the
quantity of orders and the revenue impact of the orders by region. The
Sumproduct function works great for the revenues. However, the Countif
function will only function if the corresponding Daily Order Log is open when
the values are updated, otherwise it returns "#VALUE".

The function is entered as: "=COUNTIF([04042006.xls]Master
Worksheet'!$C$3:$C$83,A4)" where Row "C" is the Region on the 04042006.xls
Spreadsheet and Row "A" is the same regions on the summary spreadsheet. When
the corresponding Daily Order Log is open, the function returns the correct

Any help would be greatly appreciated.

Need a function to look up a stock item and pick up the code from the cell immediatlley to the right. The problem is that the stock items are in different columns. Therefore I cannot use a lookup as it returns the result from one column only.
Any suggestions please.

I have been trying to find the answer on this for a couple of days now. I have a large dataset and I am trying to quickly identify the max value for the 2 different subsets. MAX function only allows 30 points so I created named ranges to be able to use the MAX function. The problem is the MAX function only returns a 0. Any suggestions would be appreciated.

Thank you,


I have two rows of formulas, the first row has a SLOPE function while the second row has CORREL. These formulas go from row A through row Z. I would like to have both formulas use the same ranges for each column. Is there a way I can set the range in the SLOPE function and then have the CORREL function automatically extract that range from it, so that any changes to SLOPE will also affect CORREL?


Iam have a list of 1 year with daily returns, and i wanted to average the months with an automatic function. The days are listed as "workdays".

So i have a lsit with

etc.. And i want to get the excel to lookup january and calcualte the average.

I tried to make an IF function that if the month=1, then average "that" range, but I couldnt not get it to work.

I tried to search this up in the forums, but was unsucsessful.

My raw data looks like this:

DATE | Return %

Thanks in advance

Hi, I have a function which returns the following error and I'm not sure why:

"Function call on left hand side of assignment must return variant or object"

Any suggestions to fix it?

The function is a copy of an existing one that works but I change the name of the function...