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!

## Similar Excel Video Tutorials

When ROUND function Must Be Used
- Learn about when the ROUND function must be used:

1) Operation is multiplying or dividing

2) You are required to round (money only has penni ...

12 Examples!
- Part 2. See 12 different examples of how to use the IF function. This video starts with IF function basics and then shows 12 different examples of how ...

IF Function Formula 16 Examples
- 1-4) IF function To Put Label on Income Statement. See four different Logical Tests for the same result.

5) IF function using comparative Operat ...

## 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:

CORREL(A1:A9,A2:A10)

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.

Thanks,

vcoder

Hi

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

Thanks!

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.

(A worksheet illustrating my issue is attached.)

I've got 3 formulas that calculate to the same value (-0.05)

= 58.1 - 58.15

= 58.4 - 58.45

= 64.4 - 64.45

Why, then, does the round function return different values?

=ROUND(58-58.05,1) returns 0

=ROUND(58.4-58.45,1) returns -0.1

=ROUND(64.4-64.45,1) returns 0

Is there some logic at work here? (Maybe I'm searching the wrong keywords, but I'm stumped.) Thanks.

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

Cheers

Hello,

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?

Hello,

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?

Hi

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)

va=VAR(Sa)

vb=VAR(Sb)

vc=VAR(Sc)

vd=VAR(Sd)

ve=VAR(Se)

cab=Covar(va,vb)

cac=Covar(va,vc)

cad=Covar(va,vd)

cae=Covar(va,ve)

cbc=Covar(vb,vc)

cbd=Covar(vb,vd)

cbe=Covar(vb,ve)

ccd=Covar(vc,vd)

cce=Covar(vc,ve)

cde=Covar(vd,ve)

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,

vcoder

Hi,

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!

Thanks

This question may require knowledge both in Excel and in finance. I have a list of the daily LIBOR quotes and i need to convert these into monthly returns. Sounds simple. Its not. I have the returns already but I cannot use the ones I have because I do not own the rights to display. Therefore, I have to figure out how to calculate them manually and check the calculation against the returns I have. in order to do this I need to figure out the price of the bond every day and use the difference in the daily price plus the coupon value to calculate the return. So essentially, I am creating a portfolio where I buy a bond and sell it the next day. It is a bond that matures in 1 month, at which time it pays coupon and face value which I have set equal to $100. I have tried using the Excel price formula to calculate the price but it can only calculate for quarterly, semi-annual, and annual bonds. The frequency will only allow the values 1, 2, and 4. I need to use 12. Does anyone know a way around this? I have already tried taking the percent change in the quote plus the value of the coupon plus one and using the Product function for all values in the month. This typically leaves me 5-10 basis points in either direction off the correct value. Any suggestions are greatly appreciated.

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

SOLEXD;12/05/2010;US;462.070

SOLEXD;11/05/2010;US;457.740

SOLEXD;10/05/2010;US;475.880

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.

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

Dima

All,

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

VB:

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!

Hi

I have a series of daily returns e.g.

0.4%

0.7%

0.2%

0.2%

-0.5%

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?

Thanks!

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

he

-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

=MIN(0,A1)

Cell B2 (and all the other ones with cell references as appropriate)

have the formula

=MIN(0,(1+B1)*(1+A2)-1)

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

Else

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

Else

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

values.

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

values.

Any help would be greatly appreciated.

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,

Dan

Hi,

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

Jan

Feb

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