Serving the Quantitative Finance Community

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 9
 
User avatar
bluetrin
Posts: 2
Joined: September 9th, 2005, 6:41 am

Excel tricks

December 13th, 2012, 9:14 am

Excel has alot of quirks, the one I hate the most are the bugs with EVALUATE and user defined functions
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

January 21st, 2013, 8:45 pm

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.
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

January 22nd, 2013, 11:13 am

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.
 
User avatar
Cuchulainn
Posts: 20250
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

January 22nd, 2013, 11:48 am

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.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

January 22nd, 2013, 4:32 pm

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 tags on January 21st, 2013, 11:00 pm, edited 1 time in total.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

January 22nd, 2013, 6:58 pm

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!
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

January 23rd, 2013, 10:52 am

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.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

January 23rd, 2013, 11:12 am

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.
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Excel tricks

January 23rd, 2013, 1:03 pm

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?
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

January 23rd, 2013, 1:55 pm

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!
 
User avatar
Cuchulainn
Posts: 20250
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

January 24th, 2013, 9:40 am

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)?
 
User avatar
Edgey
Posts: 14
Joined: March 23rd, 2005, 11:01 am

Excel tricks

January 24th, 2013, 1:27 pm

[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.