How to calculate monthly performance of stocks using daily data

Hello,

I am using excel to model an investing system.

My system relies on measuring the performance of a stock over the past 10 months (i.e. the closing price on the last trading day of the latest month minus the closing price on the last trading day of the month 10 months ago).

Obviously, this is easy to do using monthly price data. However, I need to use DAILY price data (so that I can calculate profit/loss on a daily basis).

Using Daily price data, how do I get Excel to calculate the 10 month (or other duration) performance as described above?

I can't simply use a fixed number of days (i.e. 200 days), as the number of days varies from month to month.

Please help.

Hello Dave,

Thank you for your reply.

I am not sure how I would use the functions that you listed, since the last trading day of each month is different from the last calendar day of the month.

I have attached an Excel file with daily price data for the security SPY.

On the file I have indicated the last trading day of January 2010 (the 29th) and the last trading day of November 2010 (the 30th) as examples of two dates that are 10 months apart. Next to the November date, I have explained the calculation that I am attempting to make (without hard-coding specific dates, but rather with Excel automatically using the last trading day of a given month).

Your input would be much appreciated.

Thank you to both of you for your replies and formulas. They both seem to work.

So that I can learn, rather than blindly copy your formulas, could you (both of you) please walk me through your formulas in English so that I could understand them.

I have tried breaking them up into smaller parts so that I can understand them, but I seem to be having a hard time.

Again, thank you both for your help.

Hello kvsrinivasamurthy,

Thank you for your explanation. It helps.

One question: You mention that the MATCH function will find EOMONTH or lesser date. Let's say that we are in February, and the last trading day is the 26th. If I use the EOMONTH function and instruct it to go 10 months back (which would be April) and the last trading day of April was the 30th. 30 is greater than 26, will Excel default to the 26th of April and discard the remaining days (27 to 30)?

Got it.

Thank you all for your help with this issue. It is much appreciated.