@HowUTrade i just see often Get SubscribeQuotes function return text seems not getting updated in new KiteXL (e.g. Initial status before login "User not Logged in" and after login it must return status as "TRUE"), but the status text doesn't get updated to "TRUE" even after successful login, being said so however there were no issues in getting the live Quotes.
This creates confusion at times to user whether the subscribe quotes is working or stopped.
Only way we can get rid this for now would be double clicking the function formula or if we re-paste the symbol this makes status function to display the correct status. however we have to repeat this process every time we login once..this is quite cumbersome
This is how Excel works. Excel will calculate a formula only when there is a change in the referred value/referenced cell value. Example: In Cell A1 = 5 Cell A2 = 6 Cell A3 = Sum(A1+A2)
Excel will initially calculate the formula in A3 as soon as you enter the formula. Next calculation will happen only if any of the value in A1 or A2 changes. If no changes, excel will never calculate the formula in A3 and will display the last calculated result only (In the above case it will show the initial calculation result i.e. 11).
What happens when excel calculates a cell formula or what it does mean? Calculating means, excel calls all functions that make up the formula by passing the values. In the above example, excel will call the Sum function by passing the A1 and A2 value and displays the returned value of Sum function.
Here is what happens with SubscribeQuotes function. The SubscribeQuotes function is just like Sum function, the only difference is Sum function is excel's inbuilt function where as SubscribeQuotes user defined.
When you open excel, all formulas are calculated initially to build the Calc Chain. Excel calculates all cell formulas one by one on all sheets. Excel, all along the way comes to the sheet where your SubscribeQuotes function is there. Excel call the SubscribeQuotes by passing the Exch and Trade Symbol and wait for the return from the function. SubscribeQuotes function sees that user is not logged-in (As you just opened the excel, and the excel is still loading), so returns "User not logged-in" to excel and excel displays the results in cell. Excel continues calculating all the cell formulas (Note excel still opening) After some time , excel finished calculating all cells. Excel will remember all referenced values in cell formulas. Now you can see the excel sheets.
What next? Here on wards, excel will calculate a cell formula only if the referenced value is different from the earlier one. If it is different, excel calculates the formula and stores newly passed values.
This way excel uses the resources effectively (calculates only cell formulas whose value changed). The way excel designed and working is incredible.
In the SubscribeQuotes functions, the exch and trade symbol is not changed, hence the excel doesn't calculate the formula again (after login or whatever) and displays the last result i.e. the result on initial loading. So it just displays 'User not logged-in'
Normally people confuse RTD data with UDF functions like PlaceBO, GetOrderStatus or SubscribeQuotes etc.
In RTD, Excel initially creates a connection with RTD Server (the RTD functions returns nothing instead it creates a connection to RTD server) and the connection is kept alive till you close the Excel or delete the RTD formula. RTD works on the principal of Push-Pull mechanism. Excel no need to ask for data from RTD server as the data will be automatically pushed to excel.
On other hand, UDF (or simply functions) needs to be called by excel to get the latest result from the function.
When you manually invoke the SubcribeQuotes formula by editing and pressing enter in the cell or pasting values in the cell forces the excel to calculate the formula and returns latest result.
If you want this SubscribeQuotes functions to be automatically called by excel post login, You can try the below;
1. Make the SubscribeQuotes UDF volatile means this functions will be keep on called by excel just like Now() functions, will have performance impact.
2. Pass a dummy dynamic parameter to this UDF like Ltp or something, so whenever ltp changes excel will calculate this formula. Again your are unnecessary calling which will impact performance.
3. You can write a macro, that rebuilds the CalcChain (i.e. calculates all cell formulas just like on excel opening). The sample excel sheet has already have this macro in the name 'Calculate Excel'.So you can call this macro once post successful login.
Excel is mostly single threaded, so make sure the excel (you) calculates only required formulas and that too as and when required not all the time. For example: The GetLotSize will return Lot size for the symbol passed. Since the lot size is fixed, you can call it once, that's OK. There is no logic if you call this function again and again, this simply adds load to excel.
For excel there exists only two ultimate goals w.r.t Trading. 1. Fastest data analysis (i.e. Excel calculation, your system formulas) 2. Fastest order processing (i.e. Initiate Order Request, final order status depends on API server, network traffic etc)
Pls note, you can't automate everything, certain things needs to be done manually.
@HowUTrade Best and clear explanation Many thanks.... i tried manually running the macro "Calculate Excel" after my kite login, and works perfect what you have indicated. Further I prefer to go for scheduling to run your existing macro "Calculate Excel" just once 5 minutes before mkt opens.
This is how Excel works.
Excel will calculate a formula only when there is a change in the referred value/referenced cell value.
Example:
In Cell A1 = 5
Cell A2 = 6
Cell A3 = Sum(A1+A2)
Excel will initially calculate the formula in A3 as soon as you enter the formula.
Next calculation will happen only if any of the value in A1 or A2 changes.
If no changes, excel will never calculate the formula in A3 and will display the last calculated result only (In the above case it will show the initial calculation result i.e. 11).
What happens when excel calculates a cell formula or what it does mean?
Calculating means, excel calls all functions that make up the formula by passing the values.
In the above example, excel will call the Sum function by passing the A1 and A2 value and displays the returned value of Sum function.
Here is what happens with SubscribeQuotes function.
The SubscribeQuotes function is just like Sum function, the only difference is Sum function is excel's inbuilt function where as SubscribeQuotes user defined.
When you open excel, all formulas are calculated initially to build the Calc Chain.
Excel calculates all cell formulas one by one on all sheets.
Excel, all along the way comes to the sheet where your SubscribeQuotes function is there.
Excel call the SubscribeQuotes by passing the Exch and Trade Symbol and wait for the return from the function.
SubscribeQuotes function sees that user is not logged-in (As you just opened the excel, and the excel is still loading), so returns "User not logged-in" to excel and excel displays the results in cell.
Excel continues calculating all the cell formulas (Note excel still opening)
After some time , excel finished calculating all cells.
Excel will remember all referenced values in cell formulas.
Now you can see the excel sheets.
What next?
Here on wards, excel will calculate a cell formula only if the referenced value is different from the earlier one. If it is different, excel calculates the formula and stores newly passed values.
This way excel uses the resources effectively (calculates only cell formulas whose value changed).
The way excel designed and working is incredible.
In the SubscribeQuotes functions, the exch and trade symbol is not changed, hence the excel doesn't calculate the formula again (after login or whatever) and displays the last result i.e. the result on initial loading. So it just displays 'User not logged-in'
Normally people confuse RTD data with UDF functions like PlaceBO, GetOrderStatus or SubscribeQuotes etc.
In RTD, Excel initially creates a connection with RTD Server (the RTD functions returns nothing instead it creates a connection to RTD server) and the connection is kept alive till you close the Excel or delete the RTD formula. RTD works on the principal of Push-Pull mechanism.
Excel no need to ask for data from RTD server as the data will be automatically pushed to excel.
On other hand, UDF (or simply functions) needs to be called by excel to get the latest result from the function.
When you manually invoke the SubcribeQuotes formula by editing and pressing enter in the cell or pasting values in the cell forces the excel to calculate the formula and returns latest result.
If you want this SubscribeQuotes functions to be automatically called by excel post login,
You can try the below;
1. Make the SubscribeQuotes UDF volatile means this functions will be keep on called by excel just like Now() functions, will have performance impact.
2. Pass a dummy dynamic parameter to this UDF like Ltp or something, so whenever ltp changes excel will calculate this formula. Again your are unnecessary calling which will impact performance.
3. You can write a macro, that rebuilds the CalcChain (i.e. calculates all cell formulas just like on excel opening). The sample excel sheet has already have this macro in the name 'Calculate Excel'.So you can call this macro once post successful login.
Excel is mostly single threaded, so make sure the excel (you) calculates only required formulas and that too as and when required not all the time.
For example:
The GetLotSize will return Lot size for the symbol passed.
Since the lot size is fixed, you can call it once, that's OK.
There is no logic if you call this function again and again, this simply adds load to excel.
For excel there exists only two ultimate goals w.r.t Trading.
1. Fastest data analysis (i.e. Excel calculation, your system formulas)
2. Fastest order processing (i.e. Initiate Order Request, final order status depends on API server, network traffic etc)
Pls note, you can't automate everything, certain things needs to be done manually.
Best and clear explanation Many thanks....
i tried manually running the macro "Calculate Excel" after my kite login, and works perfect what you have indicated.
Further I prefer to go for scheduling to run your existing macro "Calculate Excel" just once 5 minutes before mkt opens.
you can consider this dicussion closed.