generate cashflow statement for currency transactions + tax calculation

[wusatiuk]

Why we need it? When we buy a stock for 10 USD on date: 2021/10/01 and sell on date: 2021/12/12 for 10 USD, We won´t have any profit or loss from the "stock price perspective". But the tax authority in Austria also want to know, if i have a gain from the "currency exchange rate perspective".

example - general logic buy stock for USD 10.00 on 2021/10/01 (= 9.20 EUR) and have no USD balance at that time. sell stock for USD 10.00 on 2021/12/12 (= 9.50 EUR) and directly exchange USD to EUR after selling. We have a profit out of currency conversion: EUR 0.30

the conditional logic with "and have no USD balance at the time of buying" and "and directly exchange USD to EUR after selling" will be explained later on. seems confusing at the very beginning, but makes sense in the end. :)

data source https://docs.google.com/spreadsheets/d/1xL9GxZ5TpXgERhIxJxkpTD5m-w1mRvOf5lBV6GEDSlY/edit#gid=770943250 at DATA | FXTR (starting line 53)

But it is generally "easy", as the logic is the same with stocks & options.

excplicit currency conversions - buying and selling for currencies When we buy USDs in exchange for EURs, we have a real conversion rate as "price" for the USDs. When we sell USDs in exchange for EURs, we need a "moving average price" (identical to stocks/options) for each currency which shows, how much we previously paid for the USDs in EUR. We also have the real conversion rate at the time of selling the USDs (exchange from USD to EUR). The difference = taxable profit / loss.

implicit currency conversions - buying & selling of stocks When we buy a stock/option in USD, we have a "moving average price" for the USDs in EUR (= how much be paid for the USDs). And we have the real conversion rate USD/EUR at the time of buying. The difference is a taxable profit / loss (already at the time of buying a stock). at this time, the moving average price will not change, but the amount of USDs will change.

When we sell a stock/option in USD, we have the real conversion rate at the time of selling and the "moving average price" of all USDs we currently have in our account. Here we just need to calculate a new "moving average price". the taxation itself will happen when we explicitly convert USD to EUR.

conclusion It sounds kind of complicated, but the whole "cashflow statement" is "just" the others side of the already booked transactions (where we buy and sell stocks & options) with a similar explicit / implicit logic. The only difference is, that this is another "commodity" which is currency, instead of stocks or options. the booking are "the same" - just the "other side" of the trade when buying and selling stocks / options.

Please let´s book these transactions "manually" in a log - with each buy/sell of a stock/option including the TradeID and then use the data from "data source" above to "verify" if we have "booked" all transactions correctly. Once we do that, we should see, that we have exceptions in 3 lines. (details below)

exception there are only some "special cases" (which are not STK, not CASH and not OPT) - when you look at DATA | FXTR (starting at line 53) in the following lines: 63, 77, 78

This are dividends and other transactions with code "CTRN" (starting line 88). this will be the very last part, which we might possibly do manually in xls at the very end. we will see. If you prefer to automatically book them as well, so that we are "complete", i can also collect details and the issue.

green = STK, CASH, OPT orange (exception) = the CTRN bookings starting line 88

[agusalex]

Added currency conversion impact and moving average https://github.com/agusalex/tax_calculator/blob/main/output/currency_impact.csv Is this what we want ?

[wusatiuk]

It seems that we have already booked things correctly from the "TRNT" data. the second part would be to "check" if we have booked everything correctly with "FXTR" data. i guess it would be much easier to work together with a shared screen, to have the same understanding for the data and the same thoughts and details in mind.