Power BI Exchange

Expand all | Collapse all

Calculating Retention Rate of SEC EDGAR Filings

  • 1.  Calculating Retention Rate of SEC EDGAR Filings

    Posted 11-09-2018 10:07 AM
      |   view attached
    When a company files publicly with the SEC for an IPO and the IPO is then priced, soon thereafter it must file it's first 10-Q quarterly report.

    We pull all the filing information from the SEC which dumps into one large flat Excel file. In this example, the company priced their IPO on 2/14 and filed its first 10-Q on 5/11. The Filing Agent filed both the IPO and the first 10-Q.


    In this example the company priced their IPO on 2/09 and its first 10-Q on 5/10. The Filing Agent for the IPO was different than the 10-Q.



    We would like to be able to generate the retention rate for the filing agent for the IPO to the first 10-Q and also the first full year after the first 10-Q. That is, if Filing Agent Group 1 filed the IPO and the first 10-Q, that's a retained client. At 1 year out, did the same filing agent file the 10-Q. That would be 1 year retention.

    We have not been able to figure out the DAX and preferably as the dataset is larger, would not want to split the data into an IPO table and a 10-Q table, if possible.


    ------------------------------
    Frank Goldman
    Ephrata PA
    7172713627
    ------------------------------

    Attachment(s)

    xlsx
    2018.xlsx   15.17MB 1 version


  • 2.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Silver Contributor
    Posted 11-12-2018 05:42 AM
    Edited by Gopa Kumar Sivadasan 11-12-2018 05:45 AM
      |   view attached
    Hi @Frank Goldman

    I am not sure whether I have fully understood your issue. However, based on my understanding, below is a possible solution for the first part of your question:

    QUOTE
    That is, if Filing Agent Group 1 filed the IPO and the first 10-Q, that's a retained client.
    UNQUOTE

    The solution is to have a calculated column in the table which if the above statement is true, it gives the value "Yes" against the first 10-Q row for that Issuer. If not, it will give "No". It will create this only if the issuer has an IPO. You can use this field for your visualizations or other measures.

    Since I am seeing other form types in the sample excel sheet provided, the DAX takes into consideration only the IPO and form types 10-Q.

    Retained =
    VAR issuer = 'SEC Data'[Issuer Name]
    VAR rlvntable =
        CALCULATETABLE (
            CALCULATETABLE (
                'SEC Data',
                'SEC Data'[IPO] <> "Yes",
                'SEC Data'[Issuer Name] = issuer,
                'SEC Data'[Form Type] = "10-Q"
            ),
            ALL ( 'SEC Data' )
        )
    VAR ipotable =
        CALCULATETABLE (
            CALCULATETABLE (
                'SEC Data',
                'SEC Data'[IPO] = "Yes",
                'SEC Data'[Issuer Name] = issuer
            ),
            ALL ( 'SEC Data' )
        )
    VAR first10Q =
        CALCULATE ( MIN ( 'SEC Data'[Filing Date] ), rlvntable )
    VAR ipodate =
        CALCULATE ( MIN ( 'SEC Data'[IPO Price Date] ), ipotable )
    VAR ipoagent =
        CALCULATE ( DISTINCT ( 'SEC Data'[Filing Agent Group] ), ipotable )
    VAR initial10qagent =
        CALCULATE (
            CALCULATE (
                DISTINCT ( 'SEC Data'[Filing Agent Group] ),
                'SEC Data'[Filing Date] = first10Q
            ),
            rlvntable
        )
    RETURN
        IF (
            'SEC Data'[Filing Date] = first10Q,
            SWITCH (
                TRUE (),
                ISBLANK ( ipodate ), BLANK (),
                ipoagent = initial10qagent, "Yes",
                "No"
            ),
            BLANK ()
        )

     

    thumbnail image

    I have not fully understood the second part of your question.

    QUOTE
    At 1 year out, did the same filing agent file the 10-Q. That would be 1 year retention .
    UNQUOTE

    If you feel the above solution is what you were expecting, then you can give more information or better give an example for your 2nd part, and we can try to find a solution for that.

    I apologize If I have misunderstood your question and given a wrong solution. In any case, PFA the pbix file also.

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

    Gopa Kumar S

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

    Attachment(s)



  • 3.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Posted 11-12-2018 11:20 AM
    @Gopa Kumar Sivadasan Thank you! That will get us going in the right direction. As for part 2 of the question, we simply want to understand that if the IPO filing agent was Group X, was the 10-Q a year out from the first 10-Q done by the same filing agent Group X.

    Again, thank you. I'm not sure we would have come up with this solution on our own.



    ------------------------------
    Frank Goldman
    Donnelley Financial Solutions
    Ephrata PA
    ------------------------------



  • 4.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Silver Contributor
    Posted 11-12-2018 02:56 PM
    Edited by Gopa Kumar Sivadasan 11-12-2018 03:05 PM
      |   view attached
    Hi @Frank Goldman

    Please see if the following DAX helps you with the 2nd part of your question. It considers the 10-Q filed on any date in the same month, one year from the first 10-Q filing and check if the agent is the same or not and puts "Yes" / "No" in a calculated column.

    Year Retained = 
    VAR issuer = 'SEC Data'[Issuer Name]
    VAR rlvntable =
        CALCULATETABLE (
            CALCULATETABLE (
                'SEC Data',
                'SEC Data'[IPO] <> "Yes",
                'SEC Data'[Issuer Name] = issuer,
                'SEC Data'[Form Type] = "10-Q"
            ),
            ALL ( 'SEC Data' )
        )
    VAR first10Q =
        CALCULATE ( MIN ( 'SEC Data'[Filing Date] ), rlvntable )
    VAR rlvntmonthyear =
        FORMAT ( EDATE ( first10Q, 12 ), "MMM-YYYY" )
    VAR yearendagent =
        CALCULATE (
            DISTINCT ( 'SEC Data'[Filing Agent Group] ),
            FORMAT ( 'SEC Data'[Filing Date], "mmm-yyyy" ) = rlvntmonthyear,
            rlvntable
        )
    VAR initial10qagent =
        CALCULATE (
            CALCULATE (
                DISTINCT ( 'SEC Data'[Filing Agent Group] ),
                'SEC Data'[Filing Date] = first10Q
            ),
            rlvntable
        )
    RETURN
        IF (
            FORMAT ( 'SEC Data'[Filing Date], "mmm-yyyy" ) = rlvntmonthyear
                && 'SEC Data'[Form Type] = "10-Q",
            SWITCH ( TRUE (), initial10qagent = yearendagent, "Yes", "No" ),
            BLANK ()
        )

     

    thumbnail image


    PFA the pbix file also.



    ------------------------------
    Gopa Kumar S
    ------------------------------

    Attachment(s)



  • 5.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Posted 11-13-2018 11:20 PM
    @Gopa Kumar Sivadasan Thank you again! This has helped tremendously. I really appreciate it. ​

    ------------------------------
    Frank Goldman
    Donnelley Financial Solutions
    Ephrata PA
    ------------------------------



  • 6.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Posted 11-16-2018 01:19 PM
    Hi @Gopa Kumar Sivadasan
    I work with Frank, and have a further question.  When using the DAX you suggested I am not always picking up accurate information because of the conversion to a month 12 months from the "original Q filing"  (VAR rlvntmonthyear - FORMAT (EDATE (first10Q, 12), "MMM-YYY")  Can you suggest how to expand the date range slightly?  I have tried creating another variable so I have a month before and a month after (basically adding 11 and 13 months) and then seeing if the filing date is between, however that seems to be too much time as it errors with multiple values when it expects a single value.
    Thanks again for your assistance.
    Mitch

    ------------------------------
    Mitch Goldsmith

    SECAUCUS NJ
    ------------------------------



  • 7.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Silver Contributor
    Posted 11-18-2018 03:14 PM
      |   view attached
    Hi @Mitch Goldsmith

    I am sorry, I just saw your message today.

    If I understand you correctly, the 10-Q at year out need not be filed necessarily in the 12th month but can also be filed in the 11 or the 13th month also. If so, the DAX logic needs to be changed a bit.

    You will need to add an additional column in your date table first.

    MonthYear = 
    VAR Tmonth =
        MONTH ( 'Date Table'[Date] )
    VAR Tyear =
        YEAR ( 'Date Table'[Date] )
    RETURN
        Tmonth & "-"
            & Tyear

     

    Then, use the following DAX in your calculated column in the Transaction Table.

    Year Retained New =
    ------------------------------------------------------------
    --basic info
    VAR issuer = 'SEC Data'[Issuer Name]
    VAR rlvntable =
        CALCULATETABLE (
            CALCULATETABLE (
                'SEC Data',
                'SEC Data'[IPO] <> "Yes",
                'SEC Data'[Issuer Name] = issuer,
                'SEC Data'[Form Type] = "10-Q"
            ),
            ALL ( 'SEC Data' )
        ) 
    --------------------------------------------------------------
    --finding initial agent
    VAR first10Q =
        CALCULATE ( MIN ( 'SEC Data'[Filing Date] ), rlvntable )
    VAR initial10qagent =
        CALCULATE (
            CALCULATE (
                DISTINCT ( 'SEC Data'[Filing Agent Group] ),
                'SEC Data'[Filing Date] = first10Q
            ),
            rlvntable
        ) 
    -------------------------------------------------------------------
    --finding agent at the end of 1 year - 11 months to 13 months
    VAR tfirstdate =
        CALCULATE (
            MAX ( 'Date Table'[MonthYear] ),
            'Date Table'[Date] = EDATE ( first10Q, 11 )
        )
    VAR beginday =
        STARTOFMONTH (
            CALCULATETABLE (
                VALUES ( 'Date Table'[Date] ),
                FILTER ( ALL ( 'Date Table' ), 'Date Table'[MonthYear] = tfirstdate )
            )
        )
    VAR tlastdate =
        CALCULATE (
            MAX ( 'Date Table'[MonthYear] ),
            'Date Table'[Date] = EDATE ( first10Q, 13 )
        )
    VAR lastday =
        ENDOFMONTH (
            CALCULATETABLE (
                VALUES ( 'Date Table'[Date] ),
                FILTER ( ALL ( 'Date Table' ), 'Date Table'[MonthYear] = tlastdate )
            )
        )
    VAR yearendrlvntable =
        FILTER (
            rlvntable,
            'SEC Data'[Filing Date] >= beginday
                && 'SEC Data'[Filing Date] <= lastday
        )
    VAR max10qdate =
        CALCULATE ( MAX ( 'SEC Data'[Filing Date] ), yearendrlvntable )
    VAR yearendagent =
        CALCULATE (
            DISTINCT ( 'SEC Data'[Filing Agent Group] ),
            'SEC Data'[Filing Date] = max10qdate,
            yearendrlvntable
        ) -------------------------------------------------------------------
    RETURN
        IF (
            'SEC Data'[Filing Date] = max10qdate
                && 'SEC Data'[Form Type] = "10-Q",
            SWITCH ( TRUE (), initial10qagent = yearendagent, "Yes", "No" ),
            BLANK ()
        )

     

    thumbnail image

     

    Hope this helps. PFA the pbix file also for your reference.



    ------------------------------
    Gopa Kumar S

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

    Attachment(s)



  • 8.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Posted 11-18-2018 03:57 PM
    Thanks very much - I will give it a shot and let you know how it works
    Mitch

    ------------------------------
    Mitch Goldsmith
    DFIN
    SECAUCUS NJ
    ------------------------------



  • 9.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Posted 11-21-2018 09:39 AM
    Thanks again for the assistance.  I had to make a few minor changes but the code did get us on the correct track.

    ------------------------------
    Mitch Goldsmith

    SECAUCUS NJ
    ------------------------------



  • 10.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Silver Contributor
    Posted 11-22-2018 12:07 AM
    Hi @Mitch Goldsmith Glad to know that I could be of help. ​

    ------------------------------
    Gopa Kumar S

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



  • 11.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Posted 12-06-2018 12:50 PM
    Hello Again
    I am again having some issue regarding the data that you help me create.
    The problem is that for example to S-1 filing is on one row and the 10Q and 10K filings are on other rows. This also has the indication for retention on the rows of the 10Q or 10K.  When trying to see if the S-1 has retention indicated or not, it is difficult to connect the information because it has different dates.  Is there a way to get this information all in one row.

    Issuer     Form Type    Priced Date   1stQ retention   OneYrRetention
    ABC        S-1               1/1/2017          Yes                   No
    XYZ         S-1               12/31/2017      Yes                  Yes

    ------------------------------
    Mitch Goldsmith

    SECAUCUS NJ
    ------------------------------



  • 12.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Silver Contributor
    Posted 12-08-2018 03:10 PM
    Hi @Mitch Goldsmith
    I am sorry I have not got your issue. Is it possible to share a sample file? even an excel file with example of the issue might suffice.​

    ------------------------------
    Gopa Kumar S

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



  • 13.  RE: Calculating Retention Rate of SEC EDGAR Filings

    Silver Contributor
    Posted 12-11-2018 02:26 PM
      |   view attached
    Hi @Mitch Goldsmith

    Based on your message, my understanding is this:

    -          You need the Qtr1 retention and Year1 retention to appear in the same row as the IPO row. As against the current solution where the Qtr1 and Year1 retentions appear in their respective rows. Since these retention indicators are in different rows, counting the Issuers and how many retained in Qtr1 and Year1 is difficult to achieve;

    -          The solution is a continuation of the previous solution;

    Based on my above understanding, the solution is to tweak the previous DAX to ensure that the Qtr1 and year1 retentions appear in the same row as the IPO row. This replaces the previous calculated columns.

    thumbnail image

    PFA the pbix file also for your reference. Let me know if my understanding is correct, and if so, whether the solution solves your issue.



    ------------------------------
    Gopa Kumar S

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

    Attachment(s)