12 Replies Latest reply on Dec 7, 2017 10:36 AM by Hari Ankem

    Circular reference calculating the average of unit price of stocks

    Michel Pitz

      Hello

       

      I am struggling a bit with a simple task I suppose. I could not go further. I will explain below what I need and where I got stuck. I created a packaged worbook on Tableau 10.4 with no real values or names, so we may test with it. I also create a excel file with the calculation working, however I could not reproduce it on Tableu due to a circular reference.

       

      The scenario is the following:

      I have an excel file with stock market operations, showing the quantity bought or sold and the unit price; per person.

       

                Step 1.PNG

      Image 1: Shows that Person 1, bought 2000 stocks of ABC on 14/07 by $7,32 and 3400 stocks on 14/07 by $7,26. On 20/07, bought more  3600 stocks by $7,12 and on 26/07 sold all of the 9000 previous stocks, zeroing his portfolio, no more stocks in the stock market to operate. On 28/07 the person bought 5000 new stocks by $7,69

       

       

      I will show below a different way to view the same scenario:

       

                Step 2.PNG

      Image2: I created a calculated field Qty buy/sell just making negative when the order="SELL". Then I created a running sum of this calculated field Qty buy/sell. You can see that on day 26/07, 02/08 and 25/08 the person sold all his stocks.

       

       

      What I need?

      I need just to calculate the average of unit price of the stocks the person have in his portfolio (highlighted in yellow). That means, the average unit price of the 700 stocks + 9500 stocks, unit prices 8,87 and 8,88. Because the previous stocks have already been sold, as the Running SUM shows us "0" in red.

       

       

       

       

      Person 1 scenario above may seem like a simple task. I will show below the scenario of Person 3 portfolio and the problem I got stuck with:

       

                Step 3.PNG

      Image 3: You can see that on date 02/08 the person sold all his portfolio. I have to calculate the average of unit price after 09/08, highlighted in yellow. Note that the person sold stocks on 24/08, 70.000 and 100.000 stocks, but did not zeroed his portfolio.

      Looking at the quantities bought and sold and the Running SUM, I have to calculate the unit price for below orders:

        + 105.000 stocks on 09/08  (Running sum= 105.000)

         + 125.000 stocks on 18/08  (Running sum= 230.000)

         -    70.000 stocks on 24/08  (Running sum= 160.000)

         -  100.000 stocks on 24/08  (Running sum= 60.000)

         +     8.800 stocks on 28/08  (Running sum= 68.000)

         +   58.400 stocks on 29/08  (Running sum= 127.200)

       

       

       

       

      Now, what I did to calculate the average of the unit prices of the portfolio:

       

      Step 3.PNGStep 4.png

      Image 4: I create four new columns on Excel to calculate the average of the portfolio.

       

      • 1st column "Qty x Unit price" =  For "BUY" orders we use "Qty buy/sell" * Unit Price
      • 2nd column "Running SUM of Qty x Unit" = Calculated field with running sum of the first column "Qty x Unit price"
      • 3rd column "Running SUM of Qty" = Calculated field with running sum of the "Qty buy/sell" calculated field
      • 4th column "Average Unit Price" = This calculated field is the tricky one. For "BUY" order of 12/07, the average is calculated normally: "Running sum of QtyxUnit" / "Running sum of Qty" ($891.750 / 135.000 = 6,606).

                      However for "SELL" order of 13/07, we have to set the average price of the last "BUY" order average, the last BUY average is: 6,606. (I used lookup(Average unit price, -1) )

                      After that, returning to 1st column, for "SELL" orders, we have to calculate differently from "BUY" orders. For SELL order 13/07, instead of using the unit price of 7,1. We have to use the last "BUY" average. The total value of the "SELL" order is: Quantity 135.000 on 13/07 * the last BUY average price 6,606 (135.000 * 6,606 = $891.750).              

                      So for the SELL order, the Running SUM of QtyxUnit is 0. So the portfolio was zeroed. Then on day 21/08 29.000 stocks were bought, then the calculation continues as the same as before.

       

      On Excel was possible, on tableu occurs Circular Reference error. I can see why is occuring the circular referente, but I cannot think how can I solve this problem in another way. The main thing is that I have to use the last average of the "BUY" order on the "SELL" order to know what is my average price of BUY of my portfolio

       

      Below is the image of the tableau calculated field. I tried creating another calculated field just to get the last "BUY" average. However the calculated field "Qty x unit price" occurs Circular Reference error still.

       

      Step 5.png

       

       

       

       

      I just need to solve the circular reference, I thought it would be easy, but I can think how I can fix it.

       

      After that I have to present a table just with the average of the unit price for each person as below:

      Result.PNG

      Image 5: Balance quantity is the Running sum of Qty, the portfolio of each person (BUY - SELL)

       

       

      Thank you all in advance

        • 1. Re: Circular reference calculating the average of unit price of stocks
          Justin Larson

          ***************

          EDIT: figured out where your last ave was coming from. It's sum(Qty*Unit Price) / sum(Qty) but only for the last two rows (since last sell, or since last time balance = 0 ?)

           

          So essentially, what you need to do is create an island of data for each set of sequential BUY transactions, capped off by 1 or more sells. Buy Ave is based on running sums, and sell averages assume the value for the most recent Buy.

          If I've read that right, this may prove a real challenge in Tableau, as it has no concept of row outside of a table layout. As soon as you try to summarize to person level, the table calculations are going to throw a real wrench in your gears. If this is a SQL source, it may be a little simple custom SQL to get you to the finish line.

          ***************

           

          This is an interesting problem, and I think it's entirely possible that we could re-frame the calculation to simplify it, but I'm not quite able to get my head around where your result is coming from. I follow all of your descriptions, but in the Excel file under Person1, where you have marked "Average Unit Price =8879", I can't get that number out of the data to confirm the calculation that you are performing. Can you convert this cell to a calculation so that I can see where it's coming from?

           

          • 2. Re: Circular reference calculating the average of unit price of stocks
            Justin Larson

            also, what is your connection to in production scenario?

            • 3. Re: Circular reference calculating the average of unit price of stocks
              Michel Pitz

              Hello Justin,

               

              Much thanks for you feedback.

               

              At first I will explain my connection with the production scenario and where the data comes from. I am not the person operating the stock market on the website, I am the IT person responsible for the ERP system and Tableau at the company. There is a person in charge of the stock market, and every day he receives brokerage notes of the transactions of the day per person. Then, the transactions are typed in a system created in excel with Visual Basic programmed functions. It generates lots of reports and analysis. However my CEO required more types of reports. So, I suggested using Tableau for it, everything we do here is using Tableau, every department has more than 20 types of viz, amazing!

               

              In summary, each person has a excel file with the transactions, we have 6 person, 6 excel files. In tableau, I created a Union using wildcards returning all information gathered to work with. The excel we are working on here is a mocking one, I created one file with all people together.

               

              --------------

               

              1)  EDIT: figured out where your last ave was coming from. It's sum(Qty*Unit Price) / sum(Qty) but only for the last two rows (since last sell, or since last time balance = 0 ?)

               

              2) So essentially, what you need to do is create an island of data for each set of sequential BUY transactions, capped off by 1 or more sells. Buy Ave is based on running sums, and sell averages assume the value for the most recent Buy.

              If I've read that right, this may prove a real challenge in Tableau, as it has no concept of row outside of a table layout. As soon as you try to summarize to person level, the table calculations are going to throw a real wrench in your gears. If this is a SQL source, it may be a little simple custom SQL to get you to the finish line.

               

              1) Yes, you are correct. For person 1, the scenario is simpler, because last two buys are followed by the "0" (running sum of Qty). All his stocks were sold and them the person bought stocks in to different days. So we just need to do your calculation: sum(Qty*Unit Price) / sum(Qty)

               

              2) I imagine something like that, isolate the data after a "0" sell. Looking at person 3 example, we would isolate the highlighted in yellow data. You understanding was correct, Buy average is based on running sums, and sell averages assume the value of the most recent Buy.

              The main problem is that to calculate the Sell Qty*Unit price. That unit price of the sell is the most recent average of the buys. Giving us a circular reference.

              I thought in creating two data sources, that means, one data source with all 6 excel files, and other data source with the same 6 excel files. Connected both data sources with relationship of Stock, person name, date, order. One data source calculate what we already did here, calculating the average unit price for the buys, then the other data source gets the last average of the buys and calculates the Sell "Qty*Last Unit price of buy". Maybe doing that we do not have circular referente. Crazy ideia I believe

               

              You comment about a simple custom SQL gave me an idea. The database is not SQL, however all the data is on the excel files, and the reports are created with visual basic functions. I have none experience with Visual basic, but I know many other programming languages. I may try to create a report calculating the average unit price on excel per person, using the last buy average and calculating the new Qty*unit price for sell orders. Then I only have to show the final average on Tableu in a View.

               

              Than you again !

              • 4. Re: Circular reference calculating the average of unit price of stocks
                Justin Larson

                Performing the calculation outside of Tableau I think will be your most flexible option. I was hoping I could come up with a different way to aggregate, but that ave price based on a running figure makes it hard - if we did come up with a way to do it, it may work on a table, and straight up break when you try to visualize in a different way.

                 

                Ping back here if you get it solved that way, or abandon that track, and I can noodle on the calc a bit more.

                • 5. Re: Circular reference calculating the average of unit price of stocks
                  Justin Larson

                  Trailing thought - if you can use a legacy connection to Excel, it allows you to treat an Excel source as a database, and write SQL against it. It uses the JET engine, which can be slower with larger datasets, and does not have the whole gamut of functions that you would find with something like T-SQL or PL-SQL, but you can accomplish a lot, especially with correlated subqueries, which I believe are supported by JET.

                   

                  For the SQL savvy this is one of my favorite options. It's a shame it's not available using the standard connector Tableau moved to, but fortunately they kept the legacy connection type available.

                  • 6. Re: Circular reference calculating the average of unit price of stocks
                    Michel Pitz

                    I have not heard about legacy connections so far. I've just researched about it and did some test with the mocking excel file.

                     

                    With the mocking excel that has 6 people it worked, it seems a good idea working with custom SQL. Although I am not an expert, I do have some knowlege with it. However before trying some custom SQL, I took the 6 original excel files, and imported using legacy connection. Differently from the wildcard connection that merges all files and give me all data gathered, legacy connection created 6 data sources. Using relationships between data sources will not work fine, because I won't see a list of people and all their transaction for one stock as the image below:

                     

                    legacy.JPG

                     

                    Let me know your thoughts about 6 excel files, the option I commented below was the only I could imagine

                     

                    Thank you Justin

                    • 7. Re: Circular reference calculating the average of unit price of stocks
                      Justin Larson

                      Good point, I overlooked the Union you mentioned. You would not be able to use the wildcard union, but if there really are only 6 files in reality, creating  connection to each and identical custom SQL to each should be a manageable task. You can still Union them, you just have to each one individually, rather than the handy wildcard feature. (At bottom, drag in New Union, then set it's definition to custom SQL.)

                       

                      If this 6 turns into 60, that approach will become very cumbersome, but for finite and small number of files, not too messy.

                      • 8. Re: Circular reference calculating the average of unit price of stocks
                        Michel Pitz

                        I understand what you said, I will try it and check if it is a good way out. I believe in the future we will not have more than 6, 8 or 10 excel. Because only the staff operates on the stock market.

                         

                        Before creating this topic, a few weeks ago I tried Python, was released in one of the last updates and I was curious about the technology. The pro about it is that we can manage lists and variables, I remember implementing and returning the balance of each person to learn the technology.

                         

                        I will try implementing the average unit price of the buys and return the most recent average buy unit price to the sell order. Then in the viz, creating the running sums and averages. Maybe, it no happens the circular reference.

                         

                        I let you know if I manage to get something out of it

                         

                        Thank you

                        • 9. Re: Circular reference calculating the average of unit price of stocks
                          Hari Ankem

                          Hi Michel,

                           

                          Here is the solution you need.

                          Capture.PNG

                          It has a few calculations but is not very complex. Let me know if you do not get it, and I can explain.

                           

                          I duplicated the data to include one more stock to ensure that the calculations still work.

                           

                          Hope this helps.

                          1 of 1 people found this helpful
                          • 10. Re: Circular reference calculating the average of unit price of stocks
                            Michel Pitz

                            Hello Hari,

                             

                            Thank you for the support.

                             

                            Sorry for the delay, I promise I will check your solution and give you a feedback soon.

                            We are in a hurry here at the company !

                            • 11. Re: Circular reference calculating the average of unit price of stocks
                              Michel Pitz

                              Hello Hari

                               

                              Sorry for the delay. Your solution is perfect !

                               

                              Seeing the formulas you created seems so much easier now     but I could not figure that out at the time

                               

                              Thank you a lot !

                              • 12. Re: Circular reference calculating the average of unit price of stocks
                                Hari Ankem

                                You are welcome. Glad that it worked out.