Highlight cell if text is a duplicate within the last 31 days

[MikeKates]

I will try to keep this brief. I trade stock market options in my retirement account and I created a simple spreadsheet to help track my trades. What I'm trying to accomplish is to have the cell in column F highlight yellow whenever I enter a symbol for a new trade that I'm about to open. If I have previously traded said symbol within the last 31 days then I would like for the cell to highlight to yellow, as a reminder to not trade that symbol and choose a different symbol to trade. Or any similar method if its seems simpler, I am open to ideas. I just need a way to prevent trading the same symbol within the same month, specifically the same symbol that I previously lost on in the last 31 days...but I will get into that later. I have searched the web for a week and I've tried a few ideas but nothing seems to work. I'm using google sheets for this spreadsheet.

After all the searching I've done, this is the conditional formula for column F that I found searching this forum that I thought would work...

=and(countif(feb!f$9:f9,f9)>1,today()-feb!ac9<31

...but its not working.

I have 13 pages on 1 spreadsheet from "Jan 2021" to "Dec 2021". We are in March 2021 right now so the formula above I had entered in conditional format for cell March!F9. All pages are the same copy. The symbols column range from F9:F1000, The "exit date" column range from AC9:AC1000. I do also have a second exit date that I'd like to incorporate, but I will get into that later as well.

I figured Id start here first to keep things simple and organized, and after its working, I'll throw some more curve balls into the mix.

Seriously, thank you very much!

[D13L]

Hi Mike,
Not sure if this what you're looking for but like to help.
Here is my attempt:

=SUMPRODUCT(--($F$1:$F$1000=F1),--(($AC$1:$AC$1000)>=EDATE(AC1,-1))) > 1

I've used EDATE which subtracts by 1 month instead of subtracting 31 days.

[MikeKates]

Oh wow. I am unbelievably amazed at how genius you are! Thank you so much. That was closer than I had gotten. I had to clean up that formula a bit. Here it is....

=SUMPRODUCT(($F$9:$F$1000=F9),(($AC$9:$AC$1000)>=EDATE(AC9,-1)))>1

Its perfectly fine if its 30 days, or 1 day short. Nbd. It causes tax issues if I trade the same ticker symbol less than 31 days after selling that same symbol last. In other words, Im trying to have a warning flag alert me when I type in a ticker symbol in to cell F9, to let me know that I have traded that symbol in the past 31 days, and NOT to trade it...but 30 days works just as good. My sheets are a mess, I apologize. I recently just taught myself the basics of using formula in google sheets, Ive only been doing this less than a year now but Im definitely proud and surprise I was able to get as far as I have. I have a sheet for stocks, and option trades, and master sheets to track the total of both, and then a MASTER mastersheet that tracks everything. I know it could be much more simplified and its prob a mess, but it seems to be working for me so far...minus this one road block I got myself in to lol.

So it seems that the tickers that DONT highlight yellow, are good to trade and I havent traded them in the past 30 days. Except Row F57 (WKHS), which was opened (K57) the same day as the last WKHS was closed (AC50). F57 did not highlight yellow when it should have. The blue columns are my open/enter and close/exit dates. The formula you sent me is reading column AC which I dont enter an "exit date" into AC until once I close the trade. Only cell K57 gets a date ("open date") when the trade is opened. So Im trying to figure out some way to use this formula and get around that problem. Ive tried other idea such as creating a true/false column that reads if the symbol that i type into cell F9 was entered into Feb!F9:Feb!F1000 in the last 31 days, but I couldnt figure out how to set a range with multiple ranges & criteria that is also on a different sheet (Feb). I was hoping it would just be a simple conditional format to highlight a cell just to give me a warning sign not to place that trade. Or a formula that highlights all of the ticker symbols that I enter, on all sheets, JUST for 31 days from the day opened, then the highlight would go away on day 32. This is a tough one. Its got me stumped lol I appreciate your help, I really do
I uploaded a couple of screenshots in case you have trouble understanding what Im talking about.

[MikeKates]

I tweeked the formula a bit, switched the AC "exit date" columns to K "enter date" columns, and I'm getting closer.

=SUMPRODUCT(($F$9:$F$1000=F9),(($K$9:$K$1000)>=EDATE(K9,-1)))>1

I would have to enter the ticker symbol in column F as well as that days date. If the cell turns yellow then that tells me not to trade it, because I had already placed a trade within the last 30 days but its reading it from the "open date" (column K), not the day I exited my last trade (column AC).
I uploaded a screenshot as an example trade just to make it easy to show you. I opened ticker symbol TQQQ (F66) on 3/1/21 and because I have never traded that symbol, the cell was not highlighted. I closed it on 3/15/21 (AC66). On cell F67 I opened a new trade, same symbol, TQQQ, after I typed in the example date opened, 4/2/21, 31 days after I opened the last TQQQ trade, the trade before it highlighted yellow but the new trade did not highlight yellow when it should have, because I opened a new trade within 31 days of closing the last. Does that make sense? Im getting much closer to the end result, thank you so much!

[D13L]

Mike Kerr,
Sorry but EDate adjusts according to days of month. If that month only has 29 days it will subtract 29 days, 31 days subtract 31 days, 30 and so on.
Don't know if this will work. It's dependent that column AC is Formated as Date
Here is the adjusted code to make it absolutely 31 days.

=SUMPRODUCT(($F$9:$F$1000=F9),(($AC$9:$AC$1000)>=AC9 - 31))>1

As for your additional request. Give me time and hopefully I can provide you with something.

[MikeKates]

Im just seeing your reply now. I will give it a try and let you know.

[MikeKates]

Yes, that code worked just fine. I changed it again to read column K instead of AC,

=SUMPRODUCT(($F$9:$F$1000=F9),(($K$9:$K$1000)>K9-31))>1

but its still reading from the date I opened the last trade, not counting 31 days after the last exit. Still trying to come up with a solution but this is the closest Ive come yet! Thank you!

[MikeKates]

I also have each month on a separate sheet, and that code is only reading from this specific sheet. Kind of need it to look more like this, but this isnt working.
=SUMPRODUCT((FEB$F$9:MARCH$F$1000=F9),((FEB$K$9:FEB$K$1000)>K9-31))>1

[D13L]

Sorry Mike,

I'm having trouble deconstructing your request. Let me break it down the way I comprehend and please adjust accordingly.
Data to focus
1)Same Stock Symbol logged.
2)(K)Open Date ex: 3/2/21
3)(AC)Close Date ex: 3/31/21

Highlight stock because open within 31 days to close

1)Same Stock Symbol logged.
2)(K)Open Date ex: 2/2/21
3)(AC)Close Date ex: 3/31/21

DO NOT Highlight stock because open is OVER 31 days to close


If I read that right please try:

=SUMPRODUCT(($F$9:$F$1000=F9),(($AC$9:$AC$1000)>=K9 - 31))>1

[MikeKates]

I really appreciate you trying to understand and help me, I know its hard to understand thats why Ive tried to be as detailed as I can....Ive been racking my brain at this for weeks. Yes, You've got it right.

Let me just reiterate one of your sentence, "Highlight stock because open within 31 days to close"......Highlight stock because open within 31 days to LAST time it was closed. Yes. The rule is even more specific then that but I just didnt want to make it any more confusing than it already is. I figured if I can get a formula close to what Im looking for then I would be able to figure out the rest. Even if I need to change some things around, or add more columns for new data.

Im trying to avoid what is known in the trading and investment world as, "A Wash Sale". And to avoid it, I dont want to trade the same stock in the same 31 days since the last time I sold that same stock. Whether it was sold for a loss or a gain. It mostly pertains to stocks sold for a loss, but I didnt want to make it even more difficult than it already is. Ill just avoid trading that stock altogether if Ive traded it and closed it 31 days before opening a new one.
But I do believe your examples are right and your understanding.

[MikeKates]

I attached a screenshot of what a wash sale is if that helps

[MikeKates]

I also have tabs at the bottom of my spreadsheet. I keep sheets sorted by month, so the formula would have to adjust for that. Labled Opentrades2020, Jan, Feb, March, April, May, June, July,........Dec

[D13L]

Mike,
Its really hard for me to do this logically using only mental means. Please attach(see banner above for instruction) a file with the data for the
stock symbol, open date and close date it can be any file just something I can copy and paste.

Also I placed this code above:

=SUMPRODUCT(($F$9:$F$1000=F9),(($AC$9:$AC$1000)>=K9 - 31))>1

Please confirm if you tested this code.

I also have tabs at the bottom of my spreadsheet. I keep sheets sorted by month, so the formula would have to adjust for that. Labled Opentrades2020, Jan, Feb, March, April, May, June, July,........Dec

This code is not dependent on months. Just use the same formula on all sheets.

[bebo021999]

I wish you could attach a small piece of sample. Almost done, but need an actual format sample.

[D13L]

Hi Mike,
I just just realize that you want other sheets to be back trailed on the date as well. So please give us some small sample data at least these three: symbol, open date, close date.
Bebo (reputable so you're in good hands) is also willing to help but need some form of sample data as well.

EDIT:
I think you can add another sumproduct to equation if you want to backtrack to the previous month (Not really sure just breaking it down logically):

=SUMPRODUCT(($F$9:$F$1000=F9),(($AC$9:$AC$1000)>=K9 - 31)) + SUMPRODUCT((Jan!$F$9:$F$1000=F9),((Jan!$AC$9:$AC$1000)>=K9 - 31))>1

Disclosure: Sorry, I've only been doing (I think as described) intermediate excel for about 2-3 months spare time (only used simple arithmetic formula and create a pretty checklist) so I'm no expert by any means but wanted to grow by providing help(or jumping in the lions den).