SERVING THE QUANTITATIVE FINANCE COMMUNITY

• 1
• 2
• 3
• 4
• 5
• 6
• 8

bluetrin
Posts: 293
Joined: September 9th, 2005, 6:41 am

### Excel tricks

Excel has alot of quirks, the one I hate the most are the bugs with EVALUATE and user defined functions

tagoma
Posts: 18385
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

I am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.

bearish
Posts: 5994
Joined: February 3rd, 2011, 2:19 pm

### Excel tricks

QuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.

Cuchulainn
Posts: 63378
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

I am using ADO.NET and OleDb to populate/configure Excel sheets. It's nice trick. And any language, almost can be used. Even Java.In this way the Excel data is 'untouched by human hand'.
Last edited by Cuchulainn on January 21st, 2013, 11:00 pm, edited 1 time in total.
My C++ Boost code gives
262537412640768743.999999999999250072597198185688879353856337336990862707537410378210647910118607313

http://www.datasimfinancial.com
http://www.datasim.nl

tagoma
Posts: 18385
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

QuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)
Last edited by tagoma on January 21st, 2013, 11:00 pm, edited 1 time in total.

tagoma
Posts: 18385
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

QuoteOriginally posted by: edouardQuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)The following works:in (say) C1: YEAR(A1)in C2 then dragging down: =IF(C1="","",IF(YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))=C1,"",YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))))... but, this is ugly!

bearish
Posts: 5994
Joined: February 3rd, 2011, 2:19 pm

### Excel tricks

QuoteOriginally posted by: edouardQuoteOriginally posted by: edouardQuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)The following works:in (say) C1: YEAR(A1)in C2 then dragging down: =IF(C1="","",IF(YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))=C1,"",YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))))... but, this is ugly!I agree that it is ugly, but it doesn't actually seem to work. It will produce a list of the initial consecutive years in which you have data, but blank out everything after the first year of missing data.

tagoma
Posts: 18385
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

QuoteOriginally posted by: bearishQuoteOriginally posted by: edouardQuoteOriginally posted by: edouardQuoteOriginally posted by: bearishQuoteOriginally posted by: edouardI am working at being a bit more literate with Excel functions as I tend to resort to VBA most of the time just as if there were no other option.Say, col A is a list of dates in the format MM/DD/YYYY (the first date being in A1). Date range from 1985 to 2009, and a couple of years are just missing. Col B is for prices corresponding to the daily data.In col C, I want to get a list of the years available in col A (obviously I don't want blank cells two consecutive years). What is the smartest way to do that? (using IF and MAX functions?)Thank you.This seems to be one of those cases where a 5-line VBA function will keep your spreadsheet nice and simple, whereas the native Excel solution will be something convoluted and ugly, most likely involving another column or two with intermediate values.I agree with you. And, a pivot table would sure do the job. Nonetheless, I read such a question in a Excel test, reading "Use formulas for calculation wherever possible", and I assume one must be use here.I have come up with:=IF(YEAR(A2)>YEAR(A1),VLOOKUP(YEAR(A1)+1,A:A,1,0,YEAR(A1))I would be happy that works, but that just doesn't.I am even not sure VLOOKUP can take argument such as YEAR(A1)+1 as for the searched value (a by value/by reference issue?)The following works:in (say) C1: YEAR(A1)in C2 then dragging down: `=IF(C1="","",IF(YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))=C1,"",YEAR(VLOOKUP(DATE(C1+1,12,31),A:A,1,1))))... but, this is ugly!I agree that it is ugly, but it doesn't actually seem to work. It will produce a list of the initial consecutive years in which you have data, but blank out everything after the first year of missing data.you might be right (the +1 stuff). that works with the series i have as there actually is no year missing.

Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel tricks

QuoteOriginally posted by: outrunThis one is also a great new one I discovered:=SUM( Sheet1:Sheet10!A1:B1 )sums the values in the range A1:B1 across 10 sheets!Cool! How does Excel decide which sheets to include? Is it based on the order of the tabs? And if we have "Sheet1", "Sheet2", "Sheet3", "OutlierSheet", "Sheet4", ....., "Sheet10", then will the cells of "OutlierSheet" be included in the sum?

tagoma
Posts: 18385
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

QuoteOriginally posted by: outrunI neede to count the number of ">" chars in a string. The approach is to use the SUBSTITUTE function and replace ">" with an empty string. Then you compare the length of the new string against the old string.= LEN(A1) - LEN( SUBSTITUTE( A1, ">", "") )this one is nice!

Cuchulainn
Posts: 63378
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

QuoteOriginally posted by: outrunQuoteOriginally posted by: Traden4AlphaQuoteOriginally posted by: outrunThis one is also a great new one I discovered:=SUM( Sheet1:Sheet10!A1:B1 )sums the values in the range A1:B1 across 10 sheets!Cool! How does Excel decide which sheets to include? Is it based on the order of the tabs? And if we have "Sheet1", "Sheet2", "Sheet3", "OutlierSheet", "Sheet4", ....., "Sheet10", then will the cells of "OutlierSheet" be included in the sum?Yes, so you can e.g. create two empty sheets "begin" and "end" and move all the sheets you want to sum inbetween them.[begin, end][begin, end)(begin,end)?
My C++ Boost code gives
262537412640768743.999999999999250072597198185688879353856337336990862707537410378210647910118607313

http://www.datasimfinancial.com
http://www.datasim.nl

Edgey
Posts: 219
Joined: March 23rd, 2005, 11:01 am

### Excel tricks

[begin, end]But I usually leave the sheets begin and end blank.
Last edited by Edgey on January 23rd, 2013, 11:00 pm, edited 1 time in total.