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
Best Answer
-
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.
0
Answers
-
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.0 -
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 ..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.0 -
@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 signout3 : 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 ...0 -
@Alex.Putkov12 I just added the way to reproduce the example below, thanks in advance
0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛