Power BI Exchange

Expand all | Collapse all

Calculating Retention Rate of SEC EDGAR Filings

  • 1.  Calculating Retention Rate of SEC EDGAR Filings

    Posted 9 days ago
      |   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

    Bronze Contributor
    Posted 6 days ago
    Edited by Gopa Kumar Sivadasan 6 days ago
      |   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 6 days ago
    @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

    Bronze Contributor
    Posted 6 days ago
    Edited by Gopa Kumar Sivadasan 6 days ago
      |   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 4 days ago
    @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 2 days ago
    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

    Bronze Contributor
    Posted 7 hours ago
      |   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 6 hours ago
    Thanks very much - I will give it a shot and let you know how it works
    Mitch

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