SERVING THE QUANTITATIVE FINANCE COMMUNITY

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 8
 
User avatar
bluetrin
Posts: 292
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
tagoma
Posts: 18354
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: 5527
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: 62114
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

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
tagoma
Posts: 18354
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 tagoma on January 21st, 2013, 11:00 pm, edited 1 time in total.
 
User avatar
tagoma
Posts: 18354
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: 5527
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
tagoma
Posts: 18354
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: 23951
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
tagoma
Posts: 18354
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: 62114
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

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: 219
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.
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On