Calculation working doubt

[SONJOE JOSEPH]

Dear All Excel Experts,

I'm just working to calculate the weekly, monthly and yearly returns of Nifty. The calculation is as follows:-

Weekly = Ln(Today's Closing price ie (9285.30) ie date 5th May,17 / 5 days back date from todays closing price ie 9304.05) = -0.20%.

Monthly = Ln(Today's Closing price ie (9285.30) ie date 5th May,17 / one month back same date from todays closing price 9265.15 ie 5th April,17 = 0.22%.

Yearly = Ln(Today's Closing price ie (9285.30) ie date 5th May,17 / on year back same date from today closing price 7735.50 ie 5th May,16 = 18.26%

I want this calculations updated in the highlighted whenever i update data from 6th May,17 onwards.

Do help me out to solve this calculation. Working file attached along with this message.

Regards,

Sonjoe Joseph.

 

[Pavan Kumar Sistla]

Dear Sanjoe,

See if this works.

Thanks,
Pavan.

 

[Pavan Kumar Sistla]

Sorry, here is the updated file.

Specify holidays in the holidays field, if any and accordingly based on the last entry date gets picked up and will give weekly, monthly and yearly percentages.

Please check.

Thank You and Regards,
Pavan.

 

[bosco_yip]

Try,

1] In I1325, "Weekly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(LOOKUP(9^9,A:A)-7,A:A,B:B))

2] In J1325, "Monthly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(EDATE(LOOKUP(9^9,A:A),-1),A:A,B:B))

3] In K1325, "Yearly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(EDATE(LOOKUP(9^9,A:A),-12),A:A,B:B))

Regards
Bosco

 

[SONJOE JOSEPH]

Try,

1] In I1325, "Weekly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(LOOKUP(9^9,A:A)-7,A:A,B:B))

2] In J1325, "Monthly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(EDATE(LOOKUP(9^9,A:A),-1),A:A,B:B))

3] In K1325, "Yearly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(EDATE(LOOKUP(9^9,A:A),-12),A:A,B:B))

Regards
Bosco

Thank You Bosco as always...just working on the file and will get back in case i'm having doubts

 

[SONJOE JOSEPH]

Sorry, here is the updated file.

Specify holidays in the holidays field, if any and accordingly based on the last entry date gets picked up and will give weekly, monthly and yearly percentages.

Please check.

Thank You and Regards,
Pavan.

Thank You Pavan ....just studying the formula

 

[SONJOE JOSEPH]

Thank You Bosco as always...just working on the file and will get back in case i'm having doubts

Dear Bosco,

One doubt as per you weekly calculation u got a return of 0.11% while pavan got a weekly return of 0%. Tell me guys which is the best practice to calculate the weekly return. If there are holidays in between we should include this right. Just look into Pavan updated file.

Regards,

Sonjoe Joseph

 

[SONJOE JOSEPH]

Try,

1] In I1325, "Weekly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(LOOKUP(9^9,A:A)-7,A:A,B:B))

2] In J1325, "Monthly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(EDATE(LOOKUP(9^9,A:A),-1),A:A,B:B))

3] In K1325, "Yearly" :

=LN(LOOKUP(9^9,B:B)/LOOKUP(EDATE(LOOKUP(9^9,A:A),-12),A:A,B:B))

Regards
Bosco

Dear Bosco,

Your formula is working perfectly fine when i add new values when compared with Pavan. The only thing in my new excel sheet i have added the Nifty holidays. So please update the formula taking into account the Nifty holidays and the saturdays and sundays in a year. Do tell me which is the best way to calculate the weekly Nifty returns?

Regards,

Sonjoe Joseph

 

[SONJOE JOSEPH]

Sorry, here is the updated file.

Specify holidays in the holidays field, if any and accordingly based on the last entry date gets picked up and will give weekly, monthly and yearly percentages.

Please check.

Thank You and Regards,
Pavan.

Pavan your formula is not working attached here is the screen shot. I'm attaching a new excel file with the Nifty holidays and do take into account the saturdays and sundays in a year.

Regards,

Sonjoe Joseph.

 

[bosco_yip]

Dear Bosco,

One doubt as per you weekly calculation u got a return of 0.11% while pavan got a weekly return of 0%. Tell me guys which is the best practice to calculate the weekly return. If there are holidays in between we should include this right. Just look into Pavan updated file.

Regards,

Sonjoe Joseph

Hi Joseph,



Weekly Calculation of formula in I1325 explanation :

=LN(LOOKUP(9^9,B:B)/LOOKUP(LOOKUP(9^9,A:A)-7,A:A,B:B))

If the last date is 8-May-17, Closing Price i.e. (9285.30)

=LN(B1325/LOOKUP("1-May-17",A:A,B:B))

Weekly = LN(Last date Closing Price i.e. (9285.30)/ 7 days back from last date i.e. 1-May-17 Closing Price

=LN(9285.30/B1321)

Since 1-May-17 is a holiday, and there is no-data in your table, so the formula pick up the last date before holiday i.e. 28-Apr-17 closing price (9304.05)

=LN(9285.3/9304.05)

=-0.20%

the desired result.

This is the logic and structure of my formula design.

So it is your decision to select a formula to suit with your requirement.

Regards
Bosco

 

[Pavan Kumar Sistla]

Hi Joseph,

Please use Bosco's formula as it takes care of holidays and weekends (Saturday & Sunday) as well. I checked just now. There is no need of workday function as I have put and also there is a slight change in my formula so it appears not working.

Bosco's formula is simple and powerful. You may please use that.

Thank you very much Bosco for the crystal clear explanation

Regards,
Pavan.

 

[SONJOE JOSEPH]

Hi Joseph,

View attachment 41525

Weekly Calculation of formula in I1325 explanation :

=LN(LOOKUP(9^9,B:B)/LOOKUP(LOOKUP(9^9,A:A)-7,A:A,B:B))

If the last date is 8-May-17, Closing Price i.e. (9285.30)

=LN(B1325/LOOKUP("1-May-17",A:A,B:B))

Weekly = LN(Last date Closing Price i.e. (9285.30)/ 7 days back from last date i.e. 1-May-17 Closing Price

=LN(9285.30/B1321)

Since 1-May-17 is a holiday, and there is no-data in your table, so the formula pick up the last date before holiday i.e. 28-Apr-17 closing price (9304.05)

=LN(9285.3/9304.05)

=-0.20%

the desired result.

This is the logic and structure of my formula design.

So it is your decision to select a formula to suit with your requirement.

Regards
Bosco

Thank You Bosco and well explained.