Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

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

NOT Function - Change False to True and True to False in Excel
Change True to False and False to True with this simple function in Excel. To do this, we use the NOT function. Syn ...
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 ...
Capitalize First Letter of Every Word in a Cell - PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell.  This allows you to prepare ...

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,



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!


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 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,



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

I'm trying to create a function that chooses an "available" integer.

Basically I want a function that returns an integer less than 10, that doesn't appear in the range A1:A10.

If A1:A10 contained the numbers 1-5 in ascending order, I want this function to return any value within 6-10. If 6 is entered in the range A1:A10 the function returns 7-10 and so forth.

It should be really simple, but I can't get my head around it. Professor Google isn't very helpful on the subject.

Any help would be appreciated.


this is the formula I used:


but it returns the reference error. I think the problem is that I cannot use Indiret function on the address, but I can use Indirect on a cell with the address. However, i try to keep my spreasheet as clean as possible. Is there any way to go around this problem?

Thank you very much!

Hi all,

I'm trying to calculate one year stock returns for various stock symbols in a spreadsheet that updates generally daily. My company has a vendor that supplies an add-in that allows us to pull historical stock prices into excel by using a function that references a date in a cell. The problem is, if the reference date is a date on which the market was closed (i.e. weekend or US holiday), then I receive an error. Currently, my historical date is found via =TODAY()-364.

Is there a way to have Excel return the non-holiday/non-weekend day closest to TODAY()-364?

Thanks for any help,

Is it possible to tell excel to move to the next cell if a function calculated in A2 returns "o".

I do an array function, where I look for a value in column B. Not all cells in column B have that value (I use the search function btw), and I would like to tell excel to list only those with a result that is not "0".

So, show the result where the search function is not "0". Not by making a list of it, or by selecting unique results, or leaving out the blanks using filters, but really only list those where the calculation is not equal to "0".