14 Replies Latest reply on Aug 13, 2019 10:08 PM by Hari Ankem

    How to detect wash trades?

    Jeric Lam

      Hi All,

       

      I am stuck trying to use Tableau to detect wash trades (i.e. trades that net to 0 position because of buy/sell direction) in the context of markets trading. I have attached a workbook with some sample data included.

       

      Essentially, transactions are defined as a wash trade if a buy or sell transaction is recorded for the same product and same price within the same day. In the sample data, you can see that we have 1 wash trade as highlighted in yellow.

      Does anyone know how to use Tableau to detect this? FYI I am using Tableau version 2019.2

       

      Advice and guidance is much appreciated Have a good day!

        • 1. Re: How to detect wash trades?
          lei.chen.0

          Hello Jeric,

           

          Please try this filter.

           

          { FIXED [Product], [Date], [Price]: COUNTD([Indicator])>1}

           

           

          Regards

          Lei

          2 of 2 people found this helpful
          • 2. Re: How to detect wash trades?
            Deepak Rai

            This Works:

            Thanks

            Deepak

            If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

            • 3. Re: How to detect wash trades?
              Hari Ankem

              For a wash trade, you should have a quantity measure and also need to note that multiple purchases/sales can get squared off and so I would suggest that it should be done based on the net amount of purchase or sale of a product on a single day.

               

              Hence, I have added a quantity measure, set a value of 1 and proceeded to calculate the amount, and then using this amount to check for a wash trade.

               

              1.png

               

              Hope this helps. Updated workbook is attached for reference.

              1 of 1 people found this helpful
              • 4. Re: How to detect wash trades?
                KALPIT GOYAL

                Hi Deepak,

                 

                The same calculation is showing error in my system:

                ASCII.png

                 

                Thanks,

                Kalpit

                • 5. Re: How to detect wash trades?
                  Deepak Rai

                  Date needs to be String

                  • 6. Re: How to detect wash trades?
                    Hari Ankem

                    Deepak, if you don't mind me commenting, your solution is looking for more than 1 transaction on the same date for a product. It's not checking whether the transactions are a purchase and sale. If there are more than 1 purchases or more than 1 sales in a day of a product, they will still get flagged as a wash trade. Same issue with Lei Chen's solution too.

                    • 7. Re: How to detect wash trades?
                      Jeric Lam

                      First of all, thank you Lei Chen, Deepak and Hari for your suggestions! I tried Lei Chen's and Hari's approach, and I would say both are suitable, depending on a user's definition of 'wash trade'. Apologies Deepak, I haven't had the chance to try your approach yet but will do so as soon as I can. Personally at this moment, I am leaning more towards Hari's approach as it takes the net position of a single day into account.

                       

                      Could I pick your brains further on the 2 scenarios below? Instead of looking out for buy/sell transactions for the same product and price within the same day...

                      1. How can I incorporate a 14 days period instead of within same day? For example, Product A was bought today and sold for the same price 5 days later - since it's under 14 days, it will be flagged as a potential 'wash trade'.
                      2. How can I incorporate a range for the buying/selling price (say +/- $0.10) instead of same price? For example, Product A was bought for $1.50, and was sold for $1.55 within the same day - since the difference is within +/- $0.10, it will be flagged as a potential 'wash trade'.

                       

                      The reason why I am exploring the 2 scenarios above is because a 'smart' individual might do the above to go under the radar.

                       

                      Cheers.

                      • 8. Re: How to detect wash trades?
                        Chris McClellan

                        Deepak Rai wrote:

                         

                        Date needs to be String

                        Not a great idea at all   

                         

                        Could you use an STR function on the dates to change them to strings within the calculated field ?

                        • 9. Re: How to detect wash trades?
                          Deepak Rai

                          Yes True Chris McClellan  !!! I did  that in flow of writing a Calc, Yes Could Have used STR. Having Said that, the Indicator field in my view is redundant here, because of this thing:

                           

                          buy or sell transaction is recorded for the same product and same price within the same day

                          Hari Ankem

                          Coming to Hari's Point, You may have 10 Transactions here in a day, Still you would be able to find the last one with this:

                           

                          SO WHat Happened in Indicator field is Redundent here, in my view, I thought unconventioanlly here though, you are not bound  and supposed to agree

                           

                          Here is an example for Same data but I added 3 extra lines for demo purpose:

                           

                          This is how it Looks with my Filter, , when I focussed on this line of thinking that you would have only 2 Transactions per day:

                           

                           

                          {FIXED ASCII(Product)+ASCII(STR([Date]))+Price:COUNT(ASCII(Product)+ASCII(STR([Date]))+Price)}>1

                           

                          As you can see it is showing me all those in that day with was trade or whatever it is called.

                           

                           

                          if you just want to see the last one , A009 , Just use this:

                           

                          Now you see:

                          I got A009 , so I did Differently, but it works, and Indicator (Buy or Sell) is Irrelevant in my view here.

                          Thanks

                          Deepak

                          • 10. Re: How to detect wash trades?
                            Hari Ankem

                            A wash trade means the net of purchases and sales of a product on a date is equal to zero. So, indicator does become relevant.

                            • 11. Re: How to detect wash trades?
                              Hari Ankem

                              We can do it in any way, daily, weekly, monthly, quarterly, etc. We will just need to modify the formula for the Wash Trade Check to refer the date with which we would be grouping the dates as shown below:

                               

                              1.png

                              1.png 1.png

                              1.png  1.png

                               

                              And as far as considering the range is concerned, you can modify the comparison to see whether it's less than a certain value or percentage. Just play with it.

                               

                              Updated workbook is attached for reference.

                              1 of 1 people found this helpful
                              • 12. Re: How to detect wash trades?
                                Chris McClellan

                                Deepak Rai wrote:

                                 

                                the Indicator field in my view is redundant here, because of this thing:

                                    

                                 

                                The Indicator is HIGHLY important   As Jeric asys, a "wash trade" is "trades that net to 0 position because of buy/sell direction".  The example data shows a single BUY netted to 0 by a single SELL, so your net position at the end of the day is 0.    I'm assuming that you could have TWO buys in the same day and TWO sells in the same day ... and all 4 records would be removed if they net to 0.

                                 

                                Simply counting more than 1 transaction on the same day won't get the correct answer because the Indicator field shows that the BUYs should be added and the SELLs should be subtracted.

                                 

                                Hari Ankem's calculations seem to work fine based on screenshots and the logic looks right as well.

                                1 of 1 people found this helpful
                                • 13. Re: How to detect wash trades?
                                  Jeric Lam

                                  Thanks Hari, this is very helpful. I have a couple of clarification questions if you don't mind:

                                  • Based on your method for the time period, shouldn't what is already flagged as a wash trade in "daily" also appear for "weekly", "monthly", "quarterly" and "yearly"? So going back to your workbook, A001 and A003 should also flagged as wash trade in Monthly Wash Trades since it was already previously flagged in "daily" and "weekly". Or am I understanding the logic wrongly?

                                   

                                  • Let's say we are looking at Weekly Wash Trades. If a buy transaction happened on 10 Aug and the sell transaction happened on 11 Aug for the same product and price, this will not be identified as a wash trade under "weekly" as 11 Aug is on a new week. Unless my understanding is off, I believe this might be a potential drawback of using this method?
                                  • Is there a way to specify a X days period instead of weekly, monthly, quarterly or yearly? For example, let's say X is 14 days. If Product A was bought today and sold for same price 5 days later, it will be flagged as a wash trade as it is within 14 days of the buy date.

                                   

                                  Cheers.

                                  • 14. Re: How to detect wash trades?
                                    Hari Ankem

                                    Well, that's not how I meant to do it. I was looking clearly at a day, week, month, quarter or year independent of each other. If we have to make them dependent, then, for example, when we are doing at a weekly level, we should only be considering those transactions which have not already been flagged as wash trades at a daily level. This is possible, and will need to done as separate calculations.

                                     

                                    Flagging the transactions using a X days period may be possible and may need the data model to be restructured, but I would suggest that such solutions be done outside of Tableau.