+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

    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!
    Attached Images Attached Images

  2. #2
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

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

    Hi Mike,
    Not sure if this what you're looking for but like to help.
    Here is my attempt:
    Please Login or Register  to view this content.
    I've used EDATE which subtracts by 1 month instead of subtracting 31 days.

  3. #3
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

    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.
    Attached Images Attached Images

  4. #4
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

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

    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.

    Please Login or Register  to view this content.
    As for your additional request. Give me time and hopefully I can provide you with something.

  5. #5
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

    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!
    Attached Images Attached Images

  6. #6
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

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

  7. #7
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

    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!

  8. #8
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

    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

  9. #9
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

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

    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:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

    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.

  11. #11
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

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

  12. #12
    Registered User
    Join Date
    03-24-2021
    Location
    Rhode Island
    MS-Off Ver
    Google Sheets
    Posts
    9

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

    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

  13. #13
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

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

    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:
    Please Login or Register  to view this content.
    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.

  14. #14
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

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

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

  15. #15
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

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

    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):
    Please Login or Register  to view this content.

    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).
    Last edited by D13L; 03-31-2021 at 04:33 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 03-28-2021, 07:56 PM
  2. Replies: 7
    Last Post: 09-27-2019, 06:43 PM
  3. Highlight cell at the start of every month for the first 4 days
    By MTC2016 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-10-2017, 02:34 PM
  4. Macro to Highlight Duplicates and add Duplicate text at a column
    By sanjay1248 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-15-2016, 08:48 PM
  5. Highlight when a duplicate exists within 7 days
    By kurgon in forum Excel General
    Replies: 8
    Last Post: 07-02-2012, 09:53 AM
  6. Highlight top duplicate cell
    By blade82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2010, 08:36 AM
  7. HIghlight Duplicate text
    By centwistle in forum Excel General
    Replies: 1
    Last Post: 06-23-2010, 04:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1