Log In
Register
Home
April 18, 2020
Offline
Hi there,
Hope you can help me with a formula I want to make for my worksheet.
I am trying to make a spreadsheet to record stock transactions and to figure out gain/loss.
The spreadsheet I already made is attached.
My question is what formula to use so that I get result as “Y” for yes or “N” for No in column L (superficial loss column) as shown in the spreadsheet depending on certain criteria. Right now, I have put “N” or “Y” in column L manually.
A loss is considered superficial loss when following 2 conditions are met:
- When you sell a security for a loss,
- And you have bought the same security during the period starting 30 calendar days before the sale and ending 30 calendar days after the sale.
As you can see in the spreadsheet, highlighted cells L7, L8 and L20 are marked “Y” (manually) by me as they meet the following 3 conditions:
- Rows 7, 8 and 20 is for “Sell” transactions as shown in column A,(Cells A7, A8,A20),
- In all above 3 rows, there is corresponding loss in column I (Column I7, I8,I20),
- And, the same shares were bought either 30 days before or 30 days after the “sell” date in column A.
I did not mark cell L22 as “Y” because it meets the above 2 criteria but not the third one (i.e. the same shares were not bought either 30 days before or 30 days after the sell date.
Basically, it should give result as "Y" for yes in column L only if the above 3 criteria are met, otherwise it should give “N” result.
I am not that great at excel. I learned and made this spreadsheet as it shows now, but the formula I am asking you for help seems to be too complicated for my level.
Hope you can help. Thanks
See Answer
October 5, 2010
Offline
Hi Jay,
There's no information on your workbook about when/if a security has been bought or sold in the 30 days before or after any particular transaction. So there's no way to know the information you need in order to come up with a formula to solve your problem.
Specifically, looking at Row 22, we know that transaction was a Sell and we know it was a loss, but how do we know that those shares were not bought 30 days before or after the transaction recorded in Row 22?
You'll need to record all transactions so that you can look back at what you've traded previously. But there's no way to look forward in time to see what you will trade.
So with Row 22, if we look at things as they stand now, it is a loss because we don't know the future. But if you purchased these sames shares again within 30 days after after this Sell, you'll be retrospectively adjusting Row 22 from a loss to not being a loss.
Regards
Phil
April 18, 2020
Offline
Column B shows the date of transaction i.e. buy date or sell date or SS(short-sale) date or SSFL(short-sale buy-back) date or split date.
There is always chance that there is going to be last one or two sale transactions with loss as in Row 22. In that case, it is assumed that if there is no buy transaction in previous 30 days, then there is no buy transaction (expected) in next 30 days as well and calculations has to be done considering that (we don't know how things are going to turn out in future unless planned).
I assume that result will be updated automatically by excel for that particular cell (in this case, in cell L22) in future if a buy transaction is added in next 30 days..... or as you mentioned, it has be retrospectively adjusted from a loss to not being a loss. No choice but to do that.
Hope this answers your question. Thanks
October 5, 2010
Offline
But how do I know what shares were bought when? I don't see where the actual share is identified. Are you buying/selling the same share (company) in every transaction?
April 18, 2020
Offline
Although different securities are bought over a time, they are recorded seperately in a different worksheet. Each securities are sorted by name at the end of the year and transferred to this worksheet i.e copied to this worksheet(cross-referenced) . So yes, this worksheet is for same security.
I thought of making a single worksheet to record ( & also calculate loss & gain) for different share(company). But while doing all these calculations & also while using the worksheet routinely many times over a long period, chances are that a person may hit a button on the keyboard, or do something wrong by mistake.
To avoid that, a different worksheet is made which records the transactions of different shares chronologically by date. The plan is to check each transactions at the end of the year to make sure that there is no discrepancy & then "protect" that worksheet to avoid making any changes by mistake. Once that worksheet is accurate, then this worksheet should calculate the loss/gain correctly (main data i.e date, type of transaction, quantity, price per share, total buy/sell price will be cross-referenced to the original worksheet to maintain the accuracy) .
This way, transactions can be viewed whichever way you want to view... either chronologically, all different company shares by date, .... OR.. all shares of the same company by date.
Also, if the same company's shares are grouped together, catching any mistake visually is easier.
The worksheet which I have sent in the original post with my question is an easier stripped down version. I thought that by making it simpler, my question will be easier to understand & answer. Little did I know that it will generate more question/confusion. Sorry about that and thanks for taking time to answer my question.
October 5, 2010
Offline
Hi Jay,
You can use COUNTIFS to check the conditions (Loss and within 30 days).
=IF(AND(I7<0,COUNTIFS(B$3:$B6, ">="&B7-30,$A$3:A6, "=Buy")),"Y","N")
In the attached workbook I've entered this formula into Col L. If you want to adjust it to take account of Buy's in the 30 days after a loss you can add another condition to the COUNTIFS.
Regards
Phil
Answers Post
April 18, 2020
Offline
Thanks for the guidance.
I will try to amend the formula to take account Buy's in 30 days after a loss.
A small confusion. Did you mean "COUNTIFS" instead of "SUMIFS" when you said, "If you want to adjust it to take account of Buy's in the 30 days after a loss you can add another condition to the SUMIFS" ?
Thanks
October 5, 2010
Offline
Yeah sorry, I meant COUNTIFS! I've adjusted my reply.
April 18, 2020
Offline
So finally, I amended the formula to take account of Buys in 30 days before and after a loss and is in the attached workbook.
I had to change some data and add couple of rows to check & make sure that the formula works properly.
Also, I changed the result of the formula as "YES" instead of "Y" so that it can be detected by just looking at the worksheet.
Now, ... I don't know much about excel... so I am not sure if the formula I have created is just right or can it be simplified further? I just know that it works properly, gives correct result & serves the purpose.
Any feedback is appreciated. Thanks once again for your guidance & the time you took to help me out.
October 5, 2010
Offline
Hi Jay,
You need to wrap the 2 x COUNTIFS in an OR because you want to check both forwards or backwards in time for another Buy
=IF(AND(I7<0, OR(COUNTIFS(B$3:$B6, ">="&B7-30,$A$3:A6, "=Buy"), COUNTIFS(B8:B$100, "<="&B7+30,A8:A$100, "=Buy"))), "YES","N")
If you leave both COUNTIFS inside the AND then you'll get a false when either one of them does not contain a Buy inside 30 days, regardless of whether or not there is a Buy in the other direction i.e. forwards or backwards in time.
Cheers
Phil
1 Guest(s)
