YTD return for a moving range.

[MarcJohn]

I have a moving date range (changes daily) that I would like to compute a YTD return for. How should I lock my reference point as the first day of the year so that as the dates push down the spreadsheet the YTD return is still truly YTD?

Time/Date	Yield	CHG
=Now	0.375	-0.002
Trading day before	0.377	0.01
Trading day before....	0.367	0.02
.....	0.346	0.002
.....	0.344	-0.001
.....	0.345	0.004
....	0.341	0.004
....and so on, until...	0.337	....
First day of year	0.382

<tbody>
</tbody>

I hope this clearly explains what I'm trying to do.

Thanks

 

[05newtothis]

Hi MarcJohn,
So each day you insert a row at the top with the current days data? I am still new to Excel too and would like to know how to do this as well.
Thanks!

 

[MarcJohn]

No, I do not add a new row. My data is feed into excel via a Bloomberg add-in. It has formulas that do this automatically.

 

[Andrew Poulsom]

Here is a working example:

Excel 2010
	A	B	C	D	E
1	Date/Time	Last Yield	CHNG	YTD return	Your return
2	Jun-02	0.394	0.019	0.049	0.366125
3	May-30	0.375	-0.002
4	May-29	0.377	0.01
5	May-28	0.367	0.021
6	May-27	0.346	0.002
7	May-26	0.344	0
8	May-23	0.344	-0.001
9	Jan-01	0.382	...
10	Dec-31	10000	10000
Sheet1

Cell Formulas
Range	Formula
D2	=SUM(C2:INDEX(C:C,MATCH(DATE(YEAR(A2),1,1),A:A,-1)))
E2	=AVERAGE(B2:INDEX(B:B,MATCH(DATE(YEAR(A2),1,1),A:A,-1)))

 

[MarcJohn]

This does not work. The =Sum formula and the =Average formula you gave me give me the return for the entire columns. I only need the return from the start of the year (but need the prior data for other purposes).

 

[Andrew Poulsom]

I don't know why you think it doesn't work. You can see from my example that the data for 31 Dec is excluded (I made those numbers large deliberately).

 

[MarcJohn]

I don't know why you think it works. The formula you have above tracks the change for the entire column. I need a formula that tracks the change from 1/1/14. I cant just delete all of the info below 1/1/14 because I need it for other formulas. And I cant just select the cell that 1/1/14 is in today because it automatically moves down one row everyday. I need a formula that locks on the that specific date and tracks the changes from that date to the present date.

Your formula does not do that. The return your formula gives me is -0.349162, which is the exact number that =SUM(of the entire CHG column) gives me (which dates back 4+ years). The return I am looking for is 0.004838, which is =SUM(of CHG from today to 1/1/14).

 

[Andrew Poulsom]

I say it works because my formula clearly isn't using the entire column. It stops at 1/1/14.

Can you put your workbook on a share like Box.net and post the URL? Maybe you don't have serial dates in column A.

 

[MarcJohn]

https://www.dropbox.com/s/mrw0e09exa2oukq/Vol Charts- Cut out.xlsx

I hope that works- thats one page of the workbook- It has a few bloomberg specific formulas in there so if something looks wacky its bc it cant download the info from BB if you dont have a terminal

 

[Andrew Poulsom]

Please post an example with the links removed so that there are no #NAME? errors.

 

[Andrew Poulsom]

I removed the errors. This formula:

=SUM(H3:INDEX(H:H,MATCH(DATE(YEAR(B3),1,1),B:B,-1)))

sums from H3:H112 (B112 contains 01/01/2104). Isn't that what you want?