Login Page - Create Account

Support Board


Date/Time: Thu, 28 Mar 2024 13:53:23 +0000



Spreadsheet errors

View Count: 802

[2018-06-30 00:19:43]
User93534 - Posts: 30
Using version 1750, I see there was a problem with the spread sheet display corrected in version 1761 but not sure if this is the same issue. Getting obviously wrong outputs at places on simple formulas like if(id10.sg1@3>0,id10.sg1@3,0) and is returning a zero value, but checking the details it would indeed show a positive value for the subgraph but a zero in the cell.

More problematic in trouble shooting a model I find the issue is in the calculation:

[ IF ] IF(OR(L21=1,AND(N21=0,(ID0.SG2@20-M21)<=1.25)),... = 0 (number, integer)
  [ OR ] OR(L21=1,AND(N21=0,(ID0.SG2@20-M21)<=1.25)) = 0 (number, integer)
    [ = ] L21=1 = 0 (number, integer)
      [ L21 ] L21 = 2 (number, integer)
      [ 1 ] 1 = 1 (number, integer)
    [ AND ] AND(N21=0,(ID0.SG2@20-M21)<=1.25) = 0 (number, integer)
      [ = ] N21=0 = 1 (number, integer)
        [ N21 ] N21 = 0 (number, integer)
        [ 0 ] 0 = 0 (number, integer)
      [ <= ] (ID0.SG2@20-M21)<=1.25 = 0 (number, integer)
        [ - ] (ID0.SG2@20-M21) = 1.5 (number, double)
          [ ID0.SG2@20 ] C20 = 2724.25 (number, double)
          [ M21 ] M21 = 2722.75 (number, double)
        [ 1.25 ] 1.25 = 1.25 (number, double)
  [ 0 ] 0 = (not evaluated)

specifically the final part that correctly calculates the value initially at 1.5 and then returns a final value of 1.25

Is this the same problem and solved by updating or a separate problem that needs to be addressed or have I troubleshot this all wrong?
[2018-06-30 08:58:10]
Sierra Chart Engineering - Posts: 104368
We have not examined this specific case in detail, but a similar problem or the same problem has already been resolved. Update to the current version.
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service:
Sierra Chart Teton Futures Order Routing
[2018-06-30 11:43:34]
User93534 - Posts: 30
Updated to current version and the problem persists. It calculates correctly in most cases but very clearly misfires on occasion. Tried changing formula a bit and still returns false value.

[ <= ] (C20-M21)<=1.25 = 0 (number, integer)
          [ - ] (C20-M21) = 1.5 (number, double)
            [ C20 ] C20 = 2724.25 (number, double)
            [ M21 ] M21 = 2722.75 (number, double)
          [ 1.25 ] 1.25 = 1.25 (number, double)
[2018-06-30 16:16:33]
Sierra Chart Engineering - Posts: 104368
We need you to provide a simple example spreadsheet demonstrating the problem. Here are the instructions to attach it:
https://www.sierrachart.com/index.php?page=PostingInformation.php#AttachFile
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service:
Sierra Chart Teton Futures Order Routing
[2018-06-30 17:01:17]
User93534 - Posts: 30
As far as I can tell cell N20 should return a value of 1, returns 0 on the first if condition. The study subgraph referenced is a zig zag study set to 1.25 points, calculation mode 2, 1 bar required for reversal on the current ES contract.
[2018-06-30 17:05:24]
User93534 - Posts: 30
trying again to upload file in a diff browser,think no scrpit blocked it
Private File
[2018-07-01 08:10:52]
Sierra Chart Engineering - Posts: 104368
What cell should we look at where the formula is?
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service:
Sierra Chart Teton Futures Order Routing
[2018-07-01 10:51:58]
User93534 - Posts: 30
N3 is the source and N20 is the cell in question
[2018-07-02 23:10:07]
VladimirP - Posts: 606
The Details calculations for the cell N20 described in the post #1 shows the correct values.


specifically the final part that correctly calculates the value initially at 1.5 and then returns a final value of 1.25
The formula works correctly:

[ <= ] (C20-M21)<=1.25 = 0 (number, integer)
[ - ] (C20-M21) = 1.5 (number, double)
[ C20 ] C20 = 2724.25 (number, double)
[ M21 ] M21 = 2722.75 (number, double)
[ 1.25 ] 1.25 = 1.25 (number, double)

The cell N20 is calculated correctly.
What is an algorithm to reproduce the error, described for the cell N20?
[2018-07-03 21:26:32]
VladimirP - Posts: 606
It is a bit annoying that formulas are truncated:

[ N21 ] IF(OR(L21=1,AND(N21=0,(ID0.SG2@20-M21)<=1.25)),... = 0 (number, integer)
  [ IF ] IF(OR(L21=1,AND(N21=0,(ID0.SG2@20-M21)<=1.25)),... = N21 (reference)
    [ OR ] OR(L21=1,AND(N21=0,(ID0.SG2@20-M21)<=1.25)) = 0 (number, integer)
      [ = ] L21=1 = 0 (number, integer)
        [ L21 ] L21 = 2 (number, integer)
        [ 1 ] 1 = 1 (number, integer)
      [ AND ] AND(N21=0,(ID0.SG2@20-M21)<=1.25) = 0 (number, integer)
        [ = ] N21=0 = 1 (number, integer)
          [ N21 ] N21 = 0 (number, integer)
          [ 0 ] 0 = 0 (number, integer)
        [ <= ] (ID0.SG2@20-M21)<=1.25 = 0 (number, integer)
          [ - ] (ID0.SG2@20-M21) = 1.5 (number, double)
            [ ID0.SG2@20 ] C20 = 2724.25 (number, double)
            [ M21 ] M21 = 2722.75 (number, double)
          [ 1.25 ] 1.25 = 1.25 (number, double)
    [ 0 ] 0 = (not evaluated)
    [ IF ] IF(AND(ID0.SG1@20<ID0.SG4@20,N21=0,(C20-M21)>1.... = N21 (reference)
      [ AND ] AND(ID0.SG1@20<ID0.SG4@20,N21=0,(C20-M21)>1.25) = 0 (number, integer)
        [ < ] ID0.SG1@20<ID0.SG4@20 = 0 (number, integer)
          [ ID0.SG1@20 ] B20 = 2723.75 (number, double)
          [ ID0.SG4@20 ] E20 = 2723.75 (number, double)
        [ = ] N21=0 = (not evaluated)
        [ > ] (C20-M21)>1.25 = (not evaluated)
      [ 1 ] 1 = (not evaluated)
      [ IF ] IF(OR(N21=1,AND(L21=0,(K20-ID0.SG3@20)<=1.25)),... = N21 (reference)
        [ OR ] OR(N21=1,AND(L21=0,(K20-ID0.SG3@20)<=1.25)) = 0 (number, integer)
          [ = ] N21=1 = 0 (number, integer)
            [ N21 ] N21 = 0 (number, integer)
            [ 1 ] 1 = 1 (number, integer)
          [ AND ] AND(L21=0,(K20-ID0.SG3@20)<=1.25) = 0 (number, integer)
            [ = ] L21=0 = 0 (number, integer)
              [ L21 ] L21 = 2 (number, integer)
              [ 0 ] 0 = 0 (number, integer)
            [ <= ] (K20-ID0.SG3@20)<=1.25 = (not evaluated)
        [ 2 ] 2 = (not evaluated)
        [ N21 ] N21 = N21 (reference)

The constant truncation value (50 characters) is not convenient for long formulas. It will be much flexible to deduct this value from the Dialog width.

  // Sometimes the full string can be too long, and is usually not really
  // useful, so truncate it if it is longer than a certain length.
  const int MAX_FULL_STRING_LENGTH = 50;
  c_String LimitedFullString = DetailsRootNode.GetFullString();
  if (LimitedFullString.GetLength() > MAX_FULL_STRING_LENGTH)
  {
    LimitedFullString.Truncate(MAX_FULL_STRING_LENGTH - 3);
    LimitedFullString += "...";
  }


As an alternative to show the full (non-truncated) formula, the hint can be used. Placing mouse over the truncated item the hint will popup showing full non-truncated formula.
Date Time Of Last Edit: 2018-07-05 17:04:02

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account