SERVING THE QUANTITATIVE FINANCE COMMUNITY

  • 1
  • 2
  • 3
  • 4
  • 5
  • 8
 
User avatar
tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

Excel tricks

October 8th, 2012, 12:48 pm

on social.msdn.microsoft.com local experts confirm what i read elsewhere on the internet that is one can not change part of an array in Excel.in the present context, i wish to enumerate classes in QuantLibXL modules (=ohListEnumeratedClasses, =ohEnumeratedType, ....) .i thus don't know beforehand the length of the required array.say i arbitrarily create a 20-cell length array, but that the latter happens to be too long.if i try to delete the rows that contain no values, a dialog box say i can not change part of an array.is it really impossible?
Last edited by tagoma on October 7th, 2012, 10:00 pm, edited 1 time in total.
 
User avatar
daveangel
Posts: 17031
Joined: October 20th, 2003, 4:05 pm

Excel tricks

October 8th, 2012, 1:07 pm

QuoteOriginally posted by: edouardon social.msdn.microsoft.com local experts confirm what i read elsewhere on the internet that is one can not change part of an array in Excel.in the present context, i wish to enumerate classes in QuantLibXL modules (=ohListEnumeratedClasses, =ohEnumeratedType, ....) .i thus don't know beforehand the length of the required array.say i arbitrarily create a 20-cell length array, but that the latter happens to be too long.if i try to delete the rows that contain no values, a dialog box say i can not change part of an array.is it really impossible?cant you change the ROWS and COLUMNS in the XLOPER for the array ?
knowledge comes, wisdom lingers
 
User avatar
MattF
Posts: 925
Joined: March 14th, 2003, 7:15 pm

Excel tricks

October 8th, 2012, 3:00 pm

Implement a function that returns the size of the data. (I prefer writing to variables passed by reference as it extends to multi-dimensions rather than using return.) Now you know the size of data that will be returned you can create a suitably sized range in advance.
 
User avatar
Govert
Posts: 103
Joined: January 28th, 2006, 10:02 am

Excel tricks

October 9th, 2012, 1:25 pm

There are some workarounds for the array resizing problem - they're ugly but can be made fairly reliable.Basically you first check whether the calling range is the right size. If not then schedule a macro to run after calculation is complete, and have the macro change the size of the array formula, causing the function to be evaluated again. On recalculation the caller is now the right size, and everything calms down.The main implementation issue is to schedule the macro reliably. Your best entry back into Excel is to use the COM interface to call Application.Run, but this might fail if Excel is 'busy', e.g. if a user is editing a cell, or pressing down on the mouse button. So you need to retry if you get errors ....An implementation in C# based on Excel-DNA is described here http://excel-dna.net/2011/01/30/resizin ... lt-arrays/, and is also a C# and VB.NET sample in the Excel-DNA distribution.To be clear, you could take the Excel-DNA files (the .dna file and a renamed copy of the ExcelDna.xll host) and that would give you a standalone add-in with a =Resize(...) UDF that can wrap any array UDF and resize it automatically. The UDF returning an array need not be implemented in .NET - it can be in a C++ .xll or in VBA or whatever.-GovertExcel-DNA - Free and easy .NET for Excel
 
User avatar
Darou
Posts: 28
Joined: August 6th, 2002, 11:03 am

Excel tricks

October 9th, 2012, 7:42 pm

Try Pivot Tables: Back-testing of trading strategies with MS Excel pivot table
 
User avatar
Hansi
Posts: 3300
Joined: January 25th, 2010, 11:47 am

Excel tricks

October 9th, 2012, 7:46 pm

That post is using the word 'easiest' incorrectly....
 
User avatar
tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

Excel tricks

October 21st, 2012, 7:41 am

do you know this one?to hide the content (value, formula) of a cell, set the following custom format: ;;;tricky isn't it?
 
User avatar
DevonFangs
Posts: 3004
Joined: November 9th, 2009, 1:49 pm

Excel tricks

October 23rd, 2012, 3:43 pm

Also, a question to the world: who the hell uses automatic recalculation?
 
User avatar
Hansi
Posts: 3300
Joined: January 25th, 2010, 11:47 am

Excel tricks

October 23rd, 2012, 5:30 pm

QuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?
 
User avatar
tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

Excel tricks

October 23rd, 2012, 5:46 pm

QuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?using automatic recalculation can sometimes saves life. but, that can be boring as well (=rand())
 
User avatar
DevonFangs
Posts: 3004
Joined: November 9th, 2009, 1:49 pm

Excel tricks

October 23rd, 2012, 7:13 pm

QuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?So the answer is: in finance, nobody.
 
User avatar
Hansi
Posts: 3300
Joined: January 25th, 2010, 11:47 am

Excel tricks

October 23rd, 2012, 7:55 pm

QuoteOriginally posted by: DevonFangsQuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?So the answer is: in finance, nobody.I don't think you understand how trivial most the crap people in finance do all day is.
 
User avatar
DevonFangs
Posts: 3004
Joined: November 9th, 2009, 1:49 pm

Excel tricks

October 23rd, 2012, 8:00 pm

QuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsQuoteOriginally posted by: HansiQuoteOriginally posted by: DevonFangsAlso, a question to the world: who the hell uses automatic recalculation?People that correctly use spreadsheet for small tasks and data display and keep expensive calculations outside of a spreadsheet?So the answer is: in finance, nobody.I don't think you understand how trivial most the crap people in finance do all day is.So it is rather: in quant finance, nobody?
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