Autocorrelation Function

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!

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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 ...
Add Description Text to a UDF in Excel
How to add a description for a UDF (User Defined Function). This allows the user to understand what the function d ...
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 ...

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?


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


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

My entire current function for my Black Scholes Calling Price Model is


Function BlackScholesCallOption(Stock As Double, Exercise As Double, Time As Double, Interest As Double, Sigma As Double)

Dim a As Single
Dim b As Single
Dim c As Single
Dim d1 As Single
Dim d2 As Single

a = Log(Stock / Exercise)
b = (Interest + 0.5 * Sigma ^ 2) * Time
c = Sigma * (Time ^ 0.5)
d1 = (a + b) / c
d2 = d1 - Sigma * (Time ^ 0.5)
BlackScholesCallOption = Stock * NormSDist(d1) - (Exercise * Exp(-Interest * Time)) * NormSDist(d2)
End Function

The only problem is that my NormSDist functions that i use in the second last line is not defined. I need help defining it so that it pulls from the excel function normsdist().

Thanks in Advance.


I am trying to compute the monthly standard deviation from the daily returns across large data of a stock. Can anyone help me with the vba code to calculate automatically. I want the vba macro not the functions for the project.



I have a worksheet of data with dates in ascending order in column A, and in columns B and C there are a list of investment returns at each date for two assets:

Date Return for asset 1 Return for asset 2

I would like to create a formula that will calculate the correlation (using Excel's CORREL function) of the returns of the two assets between any two dates represented in the range. I would like the formula to refer to the start and end dates in cells d1 and e1 respectively so that I can change the dates in those two cells and have the correlation formula recalculate for the new period selected.

Can someone please help.


does anyone know why COUNT function returns zero?

I'm trying to count the number of employees and applying the function to the column with employee numbers.


I have written a function that requires an integer input, i.e.

Function MyFunction(MyInput as Integer)
End Function

When I use the function in Excel with a non-integer input the function returns the output #VALUE!

Could someone please let me know how to modify the code so that if a non-integer input was used the function would return the message "Must use integer input"

Any help would be much appreciated.



I have a data set of daily interest returns, but need weekly returns. As a matter of fact, there are public holidays, which means some weeks consist of only four or even less trading days. Luckly I have the date to each return. How can I turn it into weekly returns.



I have imported some data from the web site. It's using the character code
of 160 for the space rather than the character code of 32. I attempted to
use the TRIM function to clean this space, but it's not cleaning it out as
the LEN function returns the same number of characters with the TRIM
function as without the TRIM function. The data has this character code of
160 at the end of the numeric value that is techincally formatted as text
from when imported by the website, which I tried using the VALUE function,
but cause of this space, it returns the value error due to unrecognized

Is there any other way to address this issue via formula writing without
necessarly having to take extra steps such as the Text to Column wizard or
creating a custom function within VBA? I can create the function, but I
rather not have to.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Aaarrggghhh....I seldom ask for help, but this one has me stumped.

I have a SUMPRODUCT formula that doesn't like me.


=SUMPRODUCT(--(LEFT('Sheet1'!$B$8:$B$63,4)="60-1"),OFFSET('Sheet1'!$B$8:$B$63,0, 7 ))

Does not work:

=SUMPRODUCT(--(LEFT('Sheet1'!$B$8:$B$63,4)="60-1"),OFFSET('Sheet1'!$B$8:$B$63,0, COLUMN()*4-1 ))

Can anybody help? The COLUMN calc correctly calculates the number, 7 since this formula is in column B, but the rest of the formula then fails with a #VALUE! error because the OFFSET only returns one result instead of an array of 56 results. If I calculate (with F9) the COLUMN function area, it returns {7} rather than just 7. So, does that mean the array nature of SUMPRODUCT breaks the COLUMN function? Hmmm.....


I'm creating a spreadsheet and have had difficultly creating a function that returns the second smallest unique value.

For example if my data set is:

2, 2, 3, 3, 4, 5, 6

I'd want the function to return "3". I've tried playing around with the SMALL(array, k) function and trying to use a COUNTIF function for "k" but with no luck.

Any suggestions?

In a workbook if this function does not find the string its looking for then it returns #Value, but in VBA it doesn't seem to even recognize it as an error.

Here are a few quick examples: The sub breaks, but the function returns "#Value". What I need is a work around so that I can loop a search. Right now 1 failure returns "#Value" even if the first 10 searches in the loop worked. I've been trying to use worksheetfunction.iferror but it only works on the error if its in the workbook environment not while its still in vba.

Sub Macro1()
x = WorksheetFunction.Search("Goog", "Google")
MsgBox (x)
y = WorksheetFunction.Search("Goog", "Yahoo")
MsgBox (y)
End Sub

Function Macro1()
x = WorksheetFunction.Search("Goog", "Google")
Macro1 = x
End Function

Function Macro2()
y = WorksheetFunction.Search("Goog", "Yahoo")
Macro2 = y
End Function

Anyone know how I can get a failed search (y) to return 0, so I can keep a counter going like

x= x+y

I work in stock control and want to add a function in excel which works
out how many weeks stock I have versus sales in a range

eg if stock is 100 and the next 4 weeks sales are 40, 20, 20, 30

then I have 3.66 weeks stock

this is 40+20+20 = 3 weeks

and the remainder 20 as a percentage of 30 = 0.66

Any help would be much appreciated.....



I apologize in advance if this is a dumb question, or asked previously. I tried searching (and Googling) to no avail.

I was wondering how I can call a function (with 4 parameters, and returns a String) defined in the ThisWorkBook tab of my VBE, and call from a userform (specifically, when I click a button on the userform, I want it to use the public function to calculate something). I wanted to keep it separate because I will need to access the function from a 2nd userform later.

Thanks in advance!

Can someone answer this Question For me:

Write a function named SecondDerivative that returns the second derivative of the function at a specified x based on a central approximation. The function should take 2 arguements: the value of x where the derivative is to be evaluated and the value of DeltaX to be used for the central difference. Assume that the function is available in a VBA function termed myf(). myf() takes 1 arguement: the x value where the function is to be evaluated. For example, if the function is 3x^2+2x-1, then assume that the following code is already available:

Option Explicit
Function myf(x as double) as double
myf = 3*x^2+2*x-1
End Function

Thanks for any help,

How do you exclude zeros from being included in correlation functions?

1 0
2 3
2 0
1 0
3 0
3 3
3 2
2 3

If that was a spreadsheet, I would want to calculate the correlation between the pairs without a zero.

I have tried this formula without success.


Thank you for your help


I'm very new to VBA and so I think I have a question that should be easy to answer. What I'm trying to do is create a custom function that performs just like vlookup, except if the vlookup returns an error I would like it to show zero. Effectively, the function would mimic this formula: IF(ISERROR(VLOOKUP(...)),0,(VLOOKUP(...)).

I want to create this custom function b/c I find myself typing the above function I'd say once a week (for about a year now). So, I'm trying to create my own custom function.

Below I've tried to create my custom vlookup function, but it still acts just like the normal vlookup - if there is an error it displays an error instead of zero.

Again, I'm new to VBA so my code below is probably full of novice errors. Any help would be greatly appreciated,

Public Function ErrorVlookup(zlookupvalue, zrange, zColumnIndex, zLogic) As Single
Dim zVar As String

zVar = WorksheetFunction.vlookup(zlookupvalue, zrange, zColumnIndex, zLogic)
On Error GoTo ErrorHandler

ErrorVlookup = zVar
Exit Function

ErrorVlookup = 0

End Function

I am using the SUMIF to sum a column from a separate worksheet into the active worksheet. It returns 0 even though the criteria matches values in the range. I have attached a screen print of the fx function window. Can anyone tell me why it won't pull back the sum in the sum_range of 43212.91?

I have a custom UDF (user defined function) that I have been using for about 2 years with success. I am now trying to use it in an array formula and getting a #Value! error.

The UDF looks like this...


...where A1 is a string to be evaluated and B1 is a number. Basically, the function returns another number depending on the relationship of the two inputs. I have not had any issues in the past using it as shown above.

Now, I would like to use this function on a range of cells and sum the results. Here is what I have now...


Does anyone know why this returns a #Value! error?

I'm trying to use the offset function on one sheet to return a value from a separate sheet called Master Data.

The formula =OFFSET('Master Data'!$P$32,0,1,1,1) works just fine, the problem is that in this case $P$32 is returned from another function. How do I combine the 'Master Data'! and my function INDIRECT(ADDRESS(MATCH etc. etc)), which returns $P$32, and put that into the OFFSET function?

Side question, the INDIRECT formula returns an absolute reference, is there a way to make it not be absolute?


I am trying to create a frequency function that dynamically creates the bins and then counts the occurences for each....however, I am having some trouble getting what I have to work....

I know about the Partition function, however, my dataset will have negative values in it thus I need a custom function.

Function freq_bins(ret As Single, min As Single, max As Single, categories As Integer)
Dim Bins As Integer
Dim bin As Integer

Bins = (max - min) / categories

For i = 1 To 1
bin(i) = min
Next i

For i = 1 To categories
bin(i) = bin(1) + Bins
Next i

freq_bins = bin.Count

End Function

Any points in the right direction are greatly appreciated.

Thank you,


Hey guys..
I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact?

For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.

Any advise would be greatly appreciated, VBA is not out of the picture.


Hi everyone,
I have a nice case to prepare and need weekly riskfree interest returns on the UK treasury bond. Right now I have daily returns and want to convert them into weekly. The problem is, weeks do not always contain five trading days. How can I turn my data into weekly average riskfree interest returns?
Please, help me
kind regards

Look at a part of my spreadsheet below.

******** ******************** src="*********> *********> Microsoft Excel - UK interest daily.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 Date Interest rate weekday 2 4-Jan-88 9.87 1 3 5-Jan-88 9.99 2 4 6-Jan-88 10.05 3 5 7-Jan-88 10.09 4 6 8-Jan-88 10.13 5 7 11-Jan-88 10.05 1 8 12-Jan-88 10.07 2 9 13-Jan-88 10.28 3 10 14-Jan-88 10.11 4 11 15-Jan-88 9.98 5 12 18-Jan-88 9.87 1 13 19-Jan-88 9.96 2 14 20-Jan-88 9.91 3 15 21-Jan-88 9.86 4 16 22-Jan-88 9.71 5 17 25-Jan-88 9.7 1 18 26-Jan-88 9.76 2 19 27-Jan-88 9.69 3 20 28-Jan-88 9.56 4 21 29-Jan-88 9.55 5 22 1-Feb-88 9.74 1 23 2-Feb-88 9.62 2 24 3-Feb-88 9.64 3 25 4-Feb-88 9.68 4 26 5-Feb-88 9.75 5 27 8-Feb-88 9.93 1 28 9-Feb-88 9.85 2 UK interest daily
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

When I use a value from the Left(x,x) function, the workday(x,x,x) function does not work. It returns a NA value. If i use a number instead of the value returned from the left function, the workday function works. Anyone know how to fix this?