[Help] Calculate Turnover Trade-wise - FIFO Method (Excel VBA)

[nac]

I posted this in another thread expecting some response, but it got buried. So the reason for starting a separate thread. Hope I am not violating forum rules.

x ----------------------x----------------------x​

Hi Guys!!!

Do anyone have/know any tool for calculating turnover tradewise - FIFO method?

Zerodha is giving trade wise turnover report, pretty much every other broker only giving contract wise net position report. I have been doing this manually for some years now. I tried several times googling over the last few years to see if there is any tool/excel sheet. Though I have found some, but not much of a use. Only one was close to what I was looking, but that's not a perfect one and the calculations weren't right. Since I don't know VBA, not a lot I could do with that file.

Recently I pulled that file from archive and tried to find some solution, but couldn't understand much.

I am uploading that file, if anyone know VBA and can fix it, that would be helpful for us. Excel related forums is not much of a help in this regard. I have tried few times over the years but no response. I guess it's too complex.

Errors I could spot in the file.
- Even though, both purchase and sale qty is equal, excel throwing error saying sell qty is higher than buy qty
(in fact, it would be better if there is a qty difference on one side, code should assume the price is 0 for that qty and calculate. That includes even if there is no exit from the position i.e, only long/short entry and no exit - - sell/cover) - So this means the calculation for totaling qty is wrong.
- If we use only one ticker, it wouldn't through error but exclude the excess qty if it's in the sell side. If in buy buy side, it would show as unsold qty.
I have tried lot of things. So the data in Purchase and Sales worksheet (where we put our data) you see is not the original, that's something I entered to check.

I don't know what else are wrong in this file. But the one who made this did some good job.

PS: This file is downloaded few years ago from online when googling. No idea about who made this file or from where exactly I downloaded this file.

 

[sigrho6]

nac - first of all I want to thank you for posting this because I have been looking for something like this. Although, the credit goes to person whom actually wrote it.

I wouldn't be able able to help you with the actual coding because I am no expert. But I can provide you the below information because it worked for me (at a really very large scale as well I might add.)

The error message "sell qty is higher than buy qty" is not only buy being higher or sell being higher (if sell numbers in * total qty* higher, it will fail and that makes sense). Also and more importantly the macro has a limitation when using fifo when it has to relieve the next lot.

The way I bypassed is was...I divided all my buys and sells by a fraction of 1000. So for example, if a trade of 1000, by dividing it you get a 1. You want to do this for both tabs, purchase and sells. Then you run the macro.

Once you get the result, you just multiply the trade quantity by 1000 and you will get your result.

See Attached. Don't mind the tabs names please. I had to change it for my purposes. The headers also changed. Just use your initial file and use the multiplier and divider effect of 1000. That's really the only trick in my case which worked.

By the way - I had not use of the summary tab so I didn't pay attention to that.

Pref Calc is actually the old "Allocation" tab
Contribution is the old "purchase" tab
Distribution is old "sales" tab

I think I changed "Dim Unit_Alloc As Variant" from "Dim Unit_Alloc As Integer". Integer was giving me a rounded figure.

I am unable to attach the file because my office computer blocks uploading macro files.

Let me know how this works out for you.

Thanks

 

[nac]

Hi!
Around the time for ITR filing, I calculate turnover. So probably I wouldn't be checking this for next 8-9 months. If I get free time before and I am in mood to check this file, I will check and let you know.

In my case, it was about a dozen no exit positions last year. So I manually entered dummy exit transactions with a price of zero and changed all the symbol to one same id (NIFTY). All I needed was total turnover, no need of break up of every single scrip. So this worked for me. Yes, it was time consuming to spot no exit position and sort/arrange the data accordingly to load in this file for calculation.