A small job. Need someone to make a excel-macro (or any other way it can be done) to do a series of calculations on a sheet of excel workbook. It's a small job. Details will be discussed.
Look into the file
TurnOver (as per Income Tax) = Sum of ((abs(Profits)) + (abs(Losses)))
Profit or Loss = Sell Value - Buy Value
Buy / Sell Value = Quantity x Rate
A trade is 1 Buy transaction + 1 Sell Transaction.
On a particular date, either there's 1 trade or many.
To calculate TURNOVER trade-wise, QUANTITIES of Buy and Sell need to match.
(As one can only sell what one has bought!)
A trade is 1 Buy transaction + 1 Sell Transaction
but 1 transaction may not have happened in 1 step
take sum of G2 to G13
match the total you got with G14
ALso, if you noticed F2 to F13 says "S" meaning Sell
BUT F14 says "B" meaning Buy
Numbers won't match necessarily by dates. Because a person can buy today sell tomorrow or even later.
If total "S" quantity is matched to total "B" quantity then that's 1 trade.
The attached file has been updated.
Please refer to column "I" in blue and to its right
I have tried to calculate TURNOVER for total of 2 trades in the attached file.
Click on cell to understand formulas used
1. Calculate Amount (column "Quantity" x column "Rate")
2. Get a total of entries in column "Quantity" while referring to column "Type". So, if data starts with "S" go on adding "Quantity" until "B" is found in "Type"
3. If "B" is found in "Type" - refer to column quantity
4. Is the total "Quantity" column which has "S" = "Quantity" found for "B"
5a. If yes, populate columns "Total S" and "Total B"
5b. Calculate "Profit or Loss" in column L. Where, Profit or Loss = Total S - Total B
5c. Take the "Absolute Figure" from column L to column M
5d. Trade 1 complete
6. Move to row 15 to begin calculation for Trade 2
7. Notice -- entry F15 is "B" and G15 = 75
8. Notice -- entry F16 is "S" and G16 = 75
9. As quantities matched this becomes a complete trade. Trade number 2.
21 freelancers are bidding on average ₹1174 for this job
I can work on project with an exceptional amount of energy . I have previously executed many projects which required excellent knowledge of and experience in MS-Excel.
I have worked on several excel VBA projects and have created files for maintaining inventory, error logging and computing availability of a software based on error logging.