1
$\begingroup$

Given a simulated trading list as

 lstTrading = {{"Index", "Capitals", "Positons", "Assets", "ClosePrice", "PerAgentCost", "PerTaxCost", "ProfitRate", "ProfitAmout", "DealQuantity", "DealType"}, 
{1, 500000, 0, 500000,0, 0, 0, 0, 0, 0, " "}, 
{2, 500000., 0., 500000., 3.15, 0., 0., 0., 0., 0., " "}, 
{3, 436986., 20000., 499986., 3.15, 14.49, 0., 0., -14.49, 20000., "+"}, 
{4, 393506., 33800., 499976., 3.15, 10.,0., 0., -24.49, 13800., "+"}, 
{5, 409485., 28800., 501645., 3.2, 5., 16., 0.33, 1644.51, 5000., "-"}, 
{6, 501244., 0., 501244., 3.19, 21.13, 91.87, 0.25, 1243.51, 28800., "-"}, 
{7, 501244., 0., 501244., 3.18, 0., 0., 0.25, 1243.51, 0., "-"}};

or generally show matrix form as

lstTrading // TableForm[#, TableHeadings -> Automatic] &

enter image description here

The last column's value represents doing BUY when + or doing SELL when - or doing nothing. I want append a new column with name POSITION COST PRICE, so how to caculate the value for each row quickly? Among the columns it has Assets = Capitals + Positions*ClosePrice and when doing BUY or SELL there be AgentCost or TaxCost in the rows. There might be different formulas to calculate the result,no matter which shall be ok. Thanks!

Update: Definition v1. Caculating Position Cost Price

$$ Current \space Position \space Cost \space Price = \frac{\left(\sum buyAmount_i - \sum sellAmount_j\right)}{ latest \space Position} $$ where $$ buyAmount_i = ClosePrice_i * DealQuantity_i + ( PerAgentCost_i + PerTaxCost_i) $$ and $$ sellAmount_j = ClosePrice_j * DealQuantity_j - ( PerAgentCost_j + PerTaxCost_j) $$

when the Positon was sold out (when positon level became zero), the previous cost shall be used instead.

$\endgroup$
1
$\begingroup$

This is the type of operations that Dataset was made for, even though it is also very possible to do it with lists.

With Dataset, we would start by creating the dataset like this:

columns = Association @@ Thread[{
      "Index", "Capitals", "Positions", "Assets",
      "ClosePrice", "PerAgentCost", "PerTaxCost",
      "ProfitRate", "ProfitAmout", "DealQuantity",
      "DealType"
      } -> Range[11]];

lstTrading = {
   {1, 500000, 0, 500000, 0, 0, 0, 0, 0, 0, " "}, {2, 500000., 0., 
    500000., 3.15, 0., 0., 0., 0., 0., " "}, {3, 436986., 20000., 
    499986., 3.15, 14.49, 0., 0., -14.49, 20000., "+"}, {4, 393506., 
    33800., 499976., 3.15, 10., 0., 0., -24.49, 13800., "+"}, {5, 
    409485., 28800., 501645., 3.2, 5., 16., 0.33, 1644.51, 5000., 
    "-"}, {6, 501244., 0., 501244., 3.19, 21.13, 91.87, 0.25, 1243.51,
     28800., "-"}, {7, 501244., 0., 501244., 3.18, 0., 0., 0.25, 
    1243.51, 0., "-"}
   };

ds = Dataset[lstTrading];
ds = ds[All, columns](* Rename columns *)

Mathematica graphics

We can now add a new column like this:

ds[All, Append[#, "PositionCost" -> #PerAgentCost + #PerTaxCost] &]

Mathematica graphics

If you want to do logic that is contingent on one of the other columns, for example, do a different thing depending on whether DealType is + or -, then you do it like this:

ds = ds[All, If[
   #DealType == "+",
   Append[#, "PurchasePrice" -> #ClosePrice #Positions],
   Append[#, "PurchasePrice" -> 0]
   ] &]

Mathematica graphics

You can get your list back like this:

Prepend[
 List @@@ Normal[ds],
 Keys@First@Normal[ds]
 ]
$\endgroup$
  • $\begingroup$ Thanks! The command Dataset is useful, but how to do accumulating computation over records. I've updated definition the question, Hope that I lve exactly explained what to be computed. $\endgroup$ – Jerry Nov 25 '18 at 15:09
  • $\begingroup$ @Jerry The idea is to create a new columns for buyAmount, sellAmount and latestPosition, then use those to compute currentPositionCostPrice. Maybe I'll have time later to have a look at it. $\endgroup$ – C. E. Nov 26 '18 at 5:07
1
$\begingroup$

Firstly,define function to calculate Position Cost Price as

fCaculatePosPrice[list_?ListQ] :=
    Module[ {lstBuy, lstSell, lstPosi, posQty, posPrice},
        lstBuy = Select[list, Last[#] == "+" &];
        lstSell = Select[list, Last[#] == "-" &];
        lstPosi = Select[list[[1 ;;, 2]], # > 0 &];
        If[ Length@lstPosi > 0,
            posQty = Last@lstPosi,
            Return[0]
        ];
        posPrice = 
         If[ posQty > 
           0,
             (If[ Length@lstBuy > 0,
                  Total[Times @@@ lstBuy[[1 ;;, {4, 9}]]] + 
                   Total@Flatten@lstBuy[[1 ;;, {5, 6}]],
                  0
              ] - 
             If[ Length@lstSell > 
              0,
                 (Total[Times @@@ lstSell[[1 ;;, {4, 9}]]] - 
                 Total@Flatten@lstSell[[1 ;;, {5, 6}]]),
                 0
             ])/posQty,
             0
         ];
        Return[posPrice];
    ]

then apply fCaculatePosPrice to each step sub-list-items.

(*Pick data from lstTrading*)
lstList = Rest /@ Rest@lstTrading;

(*Group items by Steps*)
lstStep = Partition[lstList, Length@lstList, 1, -1, {}]

(*Apply fCaculatePosPrice*)
lstPPrice = 
 Prepend[Round[fCaculatePosPrice /@ Reverse@lstStep, 0.0001], 
  "PositionCostPrice"]

at last, show the result.

MapThread[Append, {lstTrading, lstPPrice}] // TableForm

$$ \tiny{ \begin{array}{cccccccccccc} \text{Index} & \text{Capitals} & \text{Positons} & \text{Assets} & \text{ClosePrice} & \text{PerAgentCost} & \text{PerTaxCost} & \text{ProfitRate} & \text{ProfitAmout} & \text{DealQuantity} & \text{DealType} & \text{PositionCostPrice} \\ 1 & 500000 & 0 & 500000 & 0 & 0 & 0 & 0 & 0 & 0 & & 0. \\ 2 & 500000. & 0. & 500000. & 3.15 & 0. & 0. & 0. & 0. & 0. & & 0. \\ 3 & 436986. & 20000. & 499986. & 3.15 & 14.49 & 0. & 0. & -14.49 & 20000. & + & 3.1507 \\ 4 & 393506. & 33800. & 499976. & 3.15 & 10. & 0. & 0. & -24.49 & 13800. & + & 3.1507 \\ 5 & 409485. & 28800. & 501645. & 3.2 & 5. & 16. & 0.33 & 1644.51 & 5000. & - & 3.1429 \\ 6 & 501244. & 0. & 501244. & 3.19 & 21.13 & 91.87 & 0.25 & 1243.51 & 28800. & - & -0.0432 \\ 7 & 501244. & 0. & 501244. & 3.18 & 0. & 0. & 0.25 & 1243.51 & 0. & - & -0.0432 \\ \end{array}} $$

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.