# Valuating sum to continue process

Question:
I have a Table with bought stock:
Name , number of bought shares , total purchase price

I have a Table with sold stocks:
Name , number of sold shares , total sell price

Now the tricky unsolved part-
Since stock can be bought on multiple lines (you stuck up on more along the way)
and stock can be sold partially .
and the tax calculation I want has a simple rule -FIFO

example:
I bought 50 BA shares at 150
I bought 50 BA shares at 180

when I sell 70 BA at 200
I have 50 at 50\$ gain
and 20 at 20\$ gain
and I remain with 30 shares of BA bought at 180
(in case I have a further new line with BA purchase and other price)

how can one implement this logic??

Hi @babagrebler, and welcome to the forum!

Would you be able to post here some sample data (doesnâ€™t have to be the real values, if it is confidential). That way it will be easier for us to look at this and help.

Thanks

2 Likes

Sure, and Thank you for your help
example of both Tables will be:

Sell Table:

since itâ€™s not that simple as grouping the deduction in order to view the taxation â€śGainâ€ť

Yes itâ€™s an interesting challenge. Iâ€™m heading out for a coffee but Iâ€™ll be mulling over this while Iâ€™m out. But somebody else might have some suggestions in the meantimeâ€¦

1 Like

So nobody came to my rescue â€¦

But it was a very good coffee, with plenty of caffeineâ€¦ so here goes! I think that somewhere in this workflow (hopefully at the bottom) are the answers you need.

I think it gives more than you asked for, but maybe the rest is useful too. Although perhaps it isnâ€™t in exactly the format you need the information, but you should be able to adapt the output from the four end nodes for your purposes:

There may be a better mathematical approach to this problem and maybe the use of iterators, but as is common for me, I try to avoid iteration if I can (which is probably why it took me so long to develop the answer).

The approach I took came from this idea: imagine for every single share purchased, you received a single share certificate, so 1584 shares resulted in 1584 certificates. Now if you lay all your certificates out on the floor in order of purchase, then when you sell them, you pick them up off the floor in that same order, and sell those shares. Well that is what this workflow does. For Stock â€śAâ€ť It takes 1584 + 1584 + 1060 shares and generates 1584 +1584 +1060 â€śbuy recordsâ€ť. It takes 1584+1500 sold shares and generates 1584+1500 sold records. It then matches them up in sequence to find out which purchased shares were sold. It then groups back the result and works out what the resultant stock values and profit/loss will be. Well, ok it is slightly more than that, but in essence that is all it does!

One last thing thoughâ€¦ please donâ€™t base your tax affairs on this without testing it out to your satisfaction! Best endeavours, but use at your own risk as I canâ€™t be held responsible if it there is an error in my calculation

KNIME_sale of shares.knwf (70.7 KB)

7 Likes

Wow!!
Either your really brilliant or itâ€™s an extreme powerful coffee
Iâ€™ll be testing and let you know - Thank you so much!!

(In case Iâ€™m arrested for taxation fraud, Iâ€™ll ask for â€śget away from jail scriptâ€ť)

5 Likes