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

 

[MarcJohn]

Yes. That is what I want. When I dont use BB formulas it gives me the correct number- when I use the Bloomberg formulas it gives me the return for the entire column. Any ideas why it would do that?

By the way, thanks for all of your help on this.

 

[Andrew Poulsom]

Can you share your workbook with the BB formulas converted to values?

 

[MarcJohn]

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

 

[Andrew Poulsom]

My formula is working in that workbook. It is summing H3:H114.

 

[MarcJohn]

Yea, thats what I said yesterday. Thank you. The only problem is that when i use that formula in the actual workbook with the BB formulas it wont give me the YTD return, just the return for the entire column. Any ideas?

 

[Andrew Poulsom]

Post a workbook where you have converted the BB formulas to values using Copy and Paste Special. The latest workbook has =NOW() in B3.

 

[MarcJohn]

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

Compare sheet 1 with sheet 2

 

[Andrew Poulsom]

That's still got Bloomberg formulas in it, but the problem seems to be that B3 has only the time, not a date and time.

 

[MarcJohn]

The BB formula asks for a Date/Time field. I am using B2 'TIME' to fill the need. Is there a DATE/TIME input I could use for B2?

 

[Andrew Poulsom]

I have no knowledge of the Bloomberg Add-In, but on Google I found "To find field mnemonics, refer to the Field Search on the Bloomberg toolbar in Excel".