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:
BUY table:

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. :coffee::slightly_smiling_face:But somebody else might have some suggestions in the meantime… :crossed_fingers:

1 Like

Hi @babagrebler

So nobody came to my rescue … :weary: :rofl:

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 :wink:


KNIME_sale of shares.knwf (70.7 KB)

7 Likes

Wow!!
Either your really brilliant or it’s an extreme powerful coffee :smile:
I get your logic - it sounds about right.
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

@babagrebler… I had several cups… :joy:

2 Likes

Works wonderful on mass scale!!
Genius !

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.