Changing a value only for the currently selected filter category
Hi – a question that has some similarities to one asked in the previous post “Addressing cells programmatically”.
I am writing a simple script to replicate an excel-style goal-seek functionality. The script is to work on a matrix with a filter category (‘PhaseName’). The idea is to change the value of a particular item ‘Decline Rate’ (incrementing it up or down), depending on the value returned by the model. Only the value of ‘decline rate’ for the ‘PhaseName’ currently selected in the filter should be changed, however.
I can use the .itemIndex property of the .getFilterItem method to obtain the index number of the currently selected ‘PhaseName’, and can get the particular value for the ‘Decline Rate’ of the currently selected ‘PhaseName’ as follows:
[code:6okzccdn]
int current = |Phase Details|.getFilterItem(|Phase Details::PhaseName|).itemIndex
|Phase Details::Combined Peak.Decline Rate|.values[current].value
[/code:6okzccdn]
However, it appears that using values with an index number in this way is a read-only property – one can’t set this to a new value.
In the earlier post “Addressing cells programmatically”, I found a useful code snippet from dom, achieving something similar by creating an ArrayList based on all of the different values of the item being changed, changing only that value corresponding to the currently selected filter category, and then writing the ArrayList back to the item. In my case, this would work as follows:
[code:6okzccdn]
def ArrayList<Item> declines = |Phase Details::Combined Peak.Decline Rate|.values
declines.set(current, |Phase Details::Combined Peak.Decline Rate|.values[current].value-increment)
|Phase Details::Combined Peak.Decline Rate|.values = declines
[/code:6okzccdn]
This does in fact work – but it seems an inordinately complicated way of doing something that users must surely want to do all the time. Is anyone able to suggest a better way of doing this?
On a related note, since when the model is complete, the filter category “PhaseName” will have several hundred ‘phases’ in it, I have a related question on the model.ensureCalculated() method. With only a few phases, calculation times for this model are generally under a second – so if the goal-seek algorithm I have put together takes 6-10 iterations to find an answer, and must calcuate each time, while it is slow, it is not unbearable. Once several hundered phases have been added to the model, a full recalc will almost certainly take well over 10 seconds, meaning 6-10 iterations will become painfully slow – frustrating since in this case, only one of those several hundred phases actually needs to be calculated. Is there any method that can be called that will calculate the model only for the currently selected phase, rather than for all of them? This would seem like an essential ability for anyone with a large, complex model…
Many thanks.
Hi
Maybe you can try this
[code:2aqta4pm]
Matrix mat = |Phase Details|
Category filterCat = |Phase Details::PhaseName|
Item itemToChange = |Phase Details::Decline Rate|
Item current = mat.getFilterItem(filterCat)
mat.getSelection(“${current.name}:${itemToChange.name}”).value = 0.4
[/code:2aqta4pm]
Matrix.getSelection(String selection) returns a node range selection for the calling Matrix given its string definition. It is smart enough to quote the node names containing non alphabetic chars.
Bye
Many thanks, Luca – this is enormously useful to know about, and I can see lots of things that this way of referencing things would make much simpler.
I assume the answer to the second part of my question – if there is a way to script a recalculation only for a currently selected filter category, or in some other way perform only a partial recalculation (in the same way as in Excel one might calculate just one worksheet, and not an entire workbook) is that there isn’t a way to do this. If that’s the case, it would make a really useful addition, were it possible, at some stage.
Janak
Hi Janak,
To respond to the second half of your question: unfortunately, scripting does not support more granular calculation control at this time. It is possible that you will be stuck with your present calculation times. However, if you would like to post your model (or a sample model that is representative of your overall process), we would be glad to take a look at it and see if we can spot any opportunities for optimization that would help cut down the time.
Regards,
Ben
Hi Janak and Ben. I add some suggestions based on my experience. I have noticed that in a relatively small model, automatic recalc mode is very efficient also during the execution of a script. In order to speed up recalculation in a more complex model, requiring iterative procedures, one may try extracting the part involved in the iterative calculation in a separate model. Data from other parts may be included via intermodel formulas or datapush+datalink (if one has datapush enabled).
Bye,
Thanks Luca – this is a very useful thought. It occurs to me that the actual section of my model that is needed to perform the goal seek calculation is very small and simple, and a separate, temporary, very small model with just these pieces could even easily and very quickly be created anew each time through code, have the goalseek run on that and return a result, then feed that result back to the main model. Doing this programatically would mean that users would not have to even know of the existence of a separate model – everything would happen behind the scenes.
I’m not very familiar with the methods of quantrix application object itself, however, which would be required to implement this – the autocomplete in the script editor only seems to give you prompts for objects at the matrix level and below, and while I’ve seen examples of scripts that create and delete new matrices, I haven’t seen any examples of scripts that programatically create and delete a new model.
In Excel VBA, one would simply do something like this:
[code:oer1edrk]
Dim Wk As Workbook
Set Wk = Workbooks.Add
[/code:oer1edrk]
Is there an equivalent in Quantrix?
Janak
Janak: the scripting API has a Model class and a Quantrix class which provides more than adequate functionality for creating, saving and switching between models. In another post, I pointed to the fact that some refinement is probably needed in order to access the Quantrix class in the current 4.1 release. I am sure that the usability and power of scripting will get better and better as it has done from early 4.0 releases.
Bye.
Thanks Luca – I see what you mean!
Ben – is it possible to find out what has happened in 4.1 that means that the quantrix class is no longer visible through the scripting API?
I have noticed that even in the 4.1 edition of the ‘Introduction to Quantrix Scripting’ pdf, the quantrix class is repeatedly referred to, and when the example of the autocomplete feature is given, the example image shows an autocomplete menu showing the quantrix class and its methods and properties.
However, as Luca says, in my own copy of 4.1, when I press Ctrl+Space, the autocomplete menu that comes up for me does not have the quantrix class, or any of its methods. Similarly, if I try to call any of the methods, an error is generated.
Access to the quantrix class would certainly seem to be fairly essential for all sorts of scripting purposes. Has this been intentionally disabled in the current release? Is it coming back soon?
Janak
Hi Janak,
Please see my reply [url=http://www.quantrix.com/forums/showthread.php?t=753:23qmiqej]to Luca’s other post[/url:23qmiqej] for a response to the question about the “quantrix” property. The fact that it is still listed in the documentation is an error on our part, so I will see about fixing that. Thank you for pointing it out.
We are still quite interested to take a look at your model and see if we can determine just why it is taking so long to calculate and whether there might be alternative solutions besides involving multiple files. Would it be possible for you to post a sample of what you are doing?
Thanks,
Ben
Hi Ben,
Many thanks – I really do appreciate the offer. While I would certainly love the feedback, and would be very happy to share the model I am working on with you and the team at Quantrix, and even with a few other forum members, since what I’m developing is ultimately for commercial use, I’m not sure I can make it completely publicly available by posting it online! Are there any other ways by which sharing might be accomplished, or does that undermine the fundamental spirit of openness of the forum?
To talk in more general terms, though, while there may be optimizations that could improve the speed of the model, ultimatley I am building an oil and gas portfolio model which takes well-by-well level inputs (say up to 40 for any given field) to calculate costs and production, runs those inputs through a highly configurable economic model capable of broadly replicating the economics of almost any hydrocarbons fiscal regime in any country in the world, and returns the cashflow results. The idea is to be able to do this for hundreds of different fields, in scores of different countries (each with different fiscal terms), and get the results back at multiple different price decks and different sensitivities to start dates and capex amounts, and also at the level of both individual fields and entire company or country portfolios. Needless to say, it’s a pretty complex model, and all the optimization in the would only reduce its calculation time so far!
Since to run a goal-seek requires iteration (usually 6-12 full recalcs to end up with the correct answer), inevitably with a model this complex, that is going to take a long time – and longer and longer as more and more oil fields are added to the model. There may well be a more elegant solution to the problem than the brute force of a goal-seek, like something using differential calculus for instance, but I haven’t had a chance to get my not-brilliantly-mathematical brain around that possibility yet. Regardless, it does seem to me that for any complex model, there will be times that one wants to script a calculation without having to wait for a full recalc of the entire model. If that could be done directly by specifying a range to recalc, that would be fantastic. If not, then the ability to simply open a new model, add a few matrices, ranges and formulae programatically, calculate the results, and then close the model and return to the main model would be a very useable alternative. But I do think something is needed to enable just a small subset of calculations to be run, without a full recalc.
Janak
Hi Ben,
One more question if I may. Is there an easy way for me to roll-back my upgrade from 4.0 to 4.1, or does that require a clean reinstall?
The more I think about this, the clearer it becomes to me that to accomplish what I need to with my model, I really do need access to the ‘quantrix’ property in scripting.
There are so many things I need to do which can’t be accomplished with a closed-form calculation, but only through an iterative, goal-seek like process, which to make calculation times manageable will require the ability to produce a temporary, stripped-down version of the model for these purposes.
For example, to calculate the breakeven price for a given asset (of the many hundreds in my model), with access to the quantrix property, I would do the following:
1 – save a temporary copy of the model
2 – in the temporary copy, delete all but one of the hundreds/thousands of different assets in the model, and delete all but one of the several different price decks
3 – run the goal-seek algorithm, which with literally many 10s of millions fewer cells to calculate (at the moment – probably hundreds of millions eventually) should now run very quickly, to obtain a breakeven price value for just one asset
4 – close (and if possible delete) the temporary copy of the model
5 – return the calculated value to the original copy of the model
The scripting setup in 4.1 is such an improvement in so many ways it seems sad to ask this, but I don’t see a way to do the things I need to do at the moment, other than revert to 4.0…
Janak
Hi Janak,
In theory, you should be able to install 4.0 and 4.1 simultaneously as long as they are in different installation directories. However, the catch is that you won’t be able to open models edited in 4.1 using 4.0. Also, a large number of bugs and missing features in 4.0 were addressed in 4.1 and you may find that some of those issues hamper your efforts almost as much.
It sounds like your calculation issues are non-trivial, but we have some pretty clever people here who would like a chance to at least take a look at the model. Your concerns about confidentiality are completely understandable. I can contact you via e-mail and we can arrange a more secure transfer.
-Ben
Just to add my experience here:
I tried to implement an iterative algorithm of a similar nature to the one that Janek describes, and hit the same issue with EnsureCalculated causing unacceptable runtimes. I ended up dropping the use of Quantrix for this situation.
I’m less convinced that using separate models is a good solution to this requirement, more granular control over recalc is what is required. I don’t understand why Quantrix cannot use the same dirty flags for recalc from within the script engine as it would use from the UI, but I guess for some reason those are not available.
However, whatever the back story, at present I don’t believe Quantrix scripting is viable to implement iterative algorithms on any realistic size of data set.
It’s just as well it has lots of other uses 
BTW, many thanks to Luca for pointing out that the range object returned by [font=”Courier New”]<matrix>.getSelection[/font] has a settable [font=”Courier New”]value [/font]property. That is not visible via autocomplete and I hadn’t found reference to it anywhere (so I wonder how he knew… ?) – great feature (pity about the documentation 
)
Regards
Simon
Hi Simon,
Would it be feasible to post the model you were working on?
I would like to mention for that record that, although Janak was not able to post his model here due to its proprietary nature, we worked with him directly outside of the forum and were able to optimize that model to the point where it was quite viable, with calculation times that were constant and reasonably fast, and not dependent on the sizes of the dimensions that had previously been giving him trouble. This didn’t require the creation of any external models, just some know-how concerning the Quantrix calculation engine. If it’s possible to post (or submit by some more confidential means) a model that is representative of your approach, we may well be able adjust it to obtain satisfactory performance.
Regards,
Ben
Just a quick note to add to Ben’s response – it’s true, the team at Quantrix were able to give me some very useful pointers to model optimization that really did make all the difference in terms of being able to do iterative algorithms like this. What I learned as a result was that EnsureCalculated method actually does do only the ‘lazy’ recalc that is done by default using the UI – the problem, for me at least, was the number of cells being inadvertently dirtied during the execution of my code.
While I had a few issues in my model like use of the indirect function, which was contributing to the wide-scale dirtying of the model and thus slowing down calc times in general, the biggest problem with speed during iteration stemmed the issue identified at the start of the forum – the limitations of the scripting API in enabling changes to an item only for the currently selected item filter category, not across all items.
Using the method I described in the original post meant I was inadvertently dirtying huge swathes of the model, by changing an entire array of cells to update only one value. By switching to a method using the <matrix>.getSelection method Luca referred to, combined with optimizing other parts of the model, I was able to get calc times not only to be acceptable, but also to be independent of the sizes of the dimensions of the model.