#bng

Surprise Naruto fans! I have never once been out of my Naruto phase in my life when it comes to the Sand Family. I even used to run an askblog for them! Every year or so I'm required to draw Gaara on principle, being my favourite character of all time. Also, I'm sure that having three adopted kids and Kankuro for a brother is like herding geese, but he wouldn't have it any other way.

Thinking about opening a new sand family askblog again too, alongside my DunMeshi BioSciences AU askblog...

Marius NEVER says the name Armand in 'Blood and Gold'. The four instances the name is mentioned are from someone else's lips (Raymond Gallant and an ex-member of the Paris coven)

Marius calls him 'Amadeo' more than 200 times. And he wrote/dictated this book after Armand told his own story and his book was published under the title 'The Vampire Armand'.

Marius disregards Armand own chosen name (and I'm not defending this, I do think the trauma behind the name is huge and Armand should have addressed this. But that's another topic), and even if this is a 'memoir', Marius could still have mentioned the change.

But no, even when he took him back to Kiev, he heard his father call him Andrei and he still had to control the narrative by saying "Amadeo answered".

I just... I need to set something on fire.

Oct '23 - symbol ~ replaced with the more explicit $self/

Implementation of the BNG4.0 spreadsheet calculator in XML and XQuery requires the translation of multiple formula expressed in Excel language. This is time-consuming process as shown in this example of one column of one table.

The formula of interest is in cell T12 of Sheet A-3 On-Site Habitat Enhancement. The heading is Distinctiveness Change.

I discovered I could copy the formula by double-clicking the cell [previously I'd tried to copy the text in the entry field but couldn't because it was locked]

=IF(E12="","",IF(AND(LEFT(O12,6)="Same d",I12>X12),"Error - Trading rules not satisfied ▲",IF(AND(LEFT(O12,6)="Same b",AND(LEFT(F12,5)<>LEFT(S12,5),I12>X12)),"Error - Trading rules not satisfied ▲",IF(AND(LEFT(O12,6)="Same h",F12<>S12),"Error - Trading rules not satisfied ▲",IF(AND(LEFT(O12,6)="Bespok",F12<>S12),"Error - Trading rules not satisfied ▲",IF(X12<I12,"Error Trading Down ▲",H12&" - "&W12))))))

Decoding the cell references and replacing them with names used in the XML version of this table, we get

=IF(~Ref="","",IF(AND(LEFT(~Baseline_Required_Action,6)="Same d",~Baseline_Distinctiveness _Score> ~Proposed_Distictiveness_Score),"Error - Trading rules not satisfied ▲", IF(AND(LEFT(~Baseline_Required_Action,6)="Same b", AND(LEFT(Baseline_Habitat,5)<> LEFT(~Proposed_Habitat,5),~Baseline_Distinctiveness_Score > ~Proposed_Distictiveness_Score)),"Error - Trading rules not satisfied ▲", IF(AND(LEFT(~Baseline_Required_Action,6)="Same h",~Baseline_Habitat <> Proposed_Habitat),"Error - Trading rules not satisfied ▲", IF(AND(LEFT(~Baseline_Required_Action,6)="Bespok",~Baseline_Habitat <> Proposed_Habitat),"Error - Trading rules not satisfied ▲", IF(~Proposed_Distinctiveness_Score < Baseline_Distinctiveness_Score ,"Error Trading Down ▲",concat(Baseline_Distinctiveness ," - " , ~Proposed_Distinctiveness)))))))

The use of LEFT() here fulfills two roles: to save testing the whole text of Required Action and to partially extract the Broad Habitat from the compound Broad Habitat - Habitat column used in the spreadsheet.

The required actions is taken from the Trading Notes of the baseline habitat which has the same set of values as Distinctiveness.

Bespoke compensation likely to be required

Same habitat required

Same broad habitat or a higher distinctiveness habitat required

Same distinctiveness or better habitat required

Compensation Not Required

Even with the addition of meaningful names, the code is still difficult to understand. One reason is that this formula either reports a failure to satisfy trading rules or the change in distinctiveness if trading rules are satisfied. These purposes are really independent and it is clearer to break the column into two- one to show the change in distinctiveness, the other to validate the trading rules.

The second reason is that the tests are for the failure of a rule rather that its success. For example, the test for the Required_Action "Same broad habitat or a higher distinctiveness habitat required"

is tested by the condition

AND(LEFT(Baseline_Habitat,5) <> LEFT($self/Proposed_Habitat,5),$self/Baseline_Distinctiveness_Score > $self/Proposed_Distinctiveness_Score))

that is

(Baseline_Broad_Habitat <> $self/Proposed_Broad_Habitat) and ($self/Baseline_Distinctiveness_Score >$self/Proposed_Distinctiveness_Score)

but its not immediately obvious that this is the negation of the rule which can be tested directly by the condition

(Baseline_Broad_Habitat= $self/Proposed_Broad_Habitat) OR($self/Baseline_Distinctiveness_Score < $self/Proposed_Distinctiveness_Score)

or better still since it is the Proposed Habitat which is being checked

(Proposed_Broad_Habitat= $self/Baseline_Broad_Habitat) OR ($self/Proposed_Distinctiveness_Score > $self/Baseline_Distinctiveness_Score)

which can now be easily seen to be a formal version of the rule itself.

If it is necessary to test for the failure of the rule, it is better to negate the rule :

Not (Proposed_Broad_Habitat= $self/Baseline_Broad_Habitat) OR ($self/Proposed_Distinctiveness_Score > $self/Baseline_Distinctiveness_Score)

The following XQuery expression is used as the compute rule in the model for validating the Trading rule:

if ($self/Baseline_Required_Action = "Bespoke compensation likely to be required") then if ($self/Proposed_Habitat = $self/Baseline_Habitat) then "OK" else "Error - Trading rules not satisfied" else if ($self/Baseline_Required_Action = "Same habitat required") then if ($self/Proposed_Habitat = $self/Baseline_Habitat) then "OK" else "Error - Trading rules not satisfied" else if ($self/Baseline_Required_Action = "Same distinctiveness or better habitat required") then if ($self/Proposed_Distinctiveness_Score >=$self/Baseline_Distinctiveness_Score) then "OK" else "Error - Trading rules not satisfied" else if ($self/Baseline_Required_Action = "Same broad habitat or a higher distinctiveness habitat required") then if ($self/Proposed_Broad_Habitat =$self/Baseline_Broad_Habitat or $self/Proposed_Distinctiveness_Score > $self/Baseline_Distinctiveness_Score) then "OK" else "Error - Trading rules not satisfied" else if ($self/Baseline_Required_Action = "Compensation Not Required") then "OK" else concat("Error - Unknown Required action: ", $self/Baseline_Required_Action)

For traceability, it is good to be able to reference this calculation back to section 5.2 of the User Guide. However, because this document is provided only as a PDF and not an HTML document, it is not possible to provide a direct deep link into this part of the document.

Sadly, traceability doesn't seem to be possible for all computations in the spreadsheet. I am unable so far to trace the very next field which validates changes to Habitat_Condition.

Of course we have to trust (or laboriously check) that the same formula adjusted for position is used on all cells in the same column and in the Off-Site version of the sheet.

XQuery language

Although the XQuery language will be unfamiliar to many, this code is likely to be readable by most computer literate folk. This is partly due to the more natural English expressions used - 'let' , 'and' 'or' with '=' having its usual meaning, but perhaps comprehensibility is in the eye of the beholder.

Oct '23