For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
3 0 2 2

VBA - Refresh matrix formula

Hello,

Application.Run "PLRefreshWorksheetEventHandler"

This line of code does not refresh Matrix Formula so my question is the following one : How do I recalculate/refresh reuters Matrix Formula with VBA ?


Matrix formula for exemple :

=FxCalcPeriod($P$4;S9;M9;"FROM:MMTRADE")

Kindly,Dorian

eikoneikon-data-apiworkspacerefinitiv-dataplatform-eikonworkspace-data-apiexcelvba
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
Accepted
131 1 0 0

I reproduced the behavior you described. This behavior is expected. FxCalcPeriod function is not recalculated on F9 in step 5 because Excel sees that the values of none of its arguments changed, hence it thinks there's no need to recalculate it. When you reenter the formula in P4 Excel treats it as an update to the argument of FxCalcPeriod and recalculates the latter. You could force Excel to recalculate the whole calculation tree by hitting Ctrl+Alt+F9 (or in VBA use Application.CalculateFull). In other words replacing F9 with Ctrl+Alt+F9 in step 5 will ensure FxCalcPeriod function in your spreadsheet gets recalculated. Or you could replace the formula in P4 with =RtToday()+RtNow(). Then FxCalcPeriod will be recalculated on F9 in step 5 because the value in P4 will change on F9. Adding current time to today's date in P4 ensures that the value returned by the formula when it's recalculated on F9 is different from the previous value, which will ensure all the dependences of P4 will be recalculated on F9 as well. Yet another option is to replace =RtToday() in P4 with =TODAY(). The latter is a built-in Excel function. It is volatile, which means Excel treats it as if its value cannot be assumed to be the same from one moment to the next even if none of its arguments changed. In this case again, hitting F9 in step 5 will have FxCalcPeriod function dependent on P4 recalculated.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
39.3k 76 11 27

By design PLRefreshWorksheetEventHandler macro only affects data retrieval functions of Eikon Excel. FxCalcPeriod is a calculation function. It does not retrieve any data from Eikon platform, instead it performs a calculation based on the inputs passed to the function as arguments. Like other calculation functions FxCalcPeriod is "refreshed" by Excel recalculation if the latter includes the range where the function resides. PLRefreshWorksheetEventHandler macro may indirectly result in a "refresh" of calculation functions such as FxCalcPeriod, if it triggers Excel recalculation that includes the range where those calculation functions reside. But in a generic case PLRefreshWorksheetEventHandler will not necessarily trigger Excel recalculation.
Long story short, "refresh" of calculation functions such as FxCalcPeriod is controlled by Excel recalculation.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
3 0 2 2

Hello this ain't exact, in fact I got the following :

Excel calculate does not refresh formula and populate value, the only way to recalculate all is to go in the cells on the right of "Trading Date", click the formula ( =RtToday() ) and press ENTER. I don't manage to do it thanks to vba and thats very annoying to do it every day with hand ... Do you have any idea of how to populate calculation after I login reuters into excel ...

Thanks in advance ..


1620286414164.png (33.4 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
131 1 0 0

My previous response is indeed accurate, although it may not specifically address your particular use case. RtToday function is refreshed by Excel recalculation.
Excel recalculation is a very complicated subject. From the screenshot you provided I cannot see the full picture and cannot tell you what may be wrong. The only way I can do the latter is if I can replicate the issue on my end. One thing that may be affecting you is that quite commonly Excel recalculation tree becomes corrupt. In this case you can try rebuilding the calc tree using Ctrl+Alt+F9 shortcut. So, if you recalculate a worksheet and, contrary to expectations, some of the functions fail to update, try rebuilding the calc tree and see if that solves the problem.
If you'd like me to try reproducing the behavior you experience, please share the spreadsheet I could use for replication and provide detailed replication procedure I need to follow.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Alex.Putkov12 I just added the way to reproduce the example below, thanks in advance

Upvotes
3 0 2 2

@Alex Putkov.sure,

1 : Open the following excel (Here to download : https://we.tl/t-TTshCaMnX0 )
2 : Go in the reuters tab in ribbon the click signout

3 : Click in Cells P4 then click at the end of the formula and then press ENTER ( you should get message in excel as shown on the last screenshot
4 : Go in the reuters tab in ribbon then click offline
5 : Press F9 and "Not Signed" In will stay, do as step 3 and the "Not Signed in" will disappear and leave place to value ...


1620322480136.png (13.6 KiB)
1620323036978.png (122.5 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.