Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create fiscal years from years and months

    Dear Statalist,


    I would like to create a fiscal year variable that take values like 2005-06, 2006-07 and so on till 2018-19, from the variables year, month as given below.
    Code:
    year    month
    2006    Jan
    2006    Feb
    2006    Mar
    2006    Apr
    2006    May
    2006    Jun
    2006    Jul
    2006    Aug
    2006    Sep
    2006    Oct
    2006    Nov
    2006    Dec
    2007    Jan
    2007    Feb
    2007    Mar
    2007    Apr
    2007    May
    2007    Jun
    2007    Jul
    ---------
    ---------
    2019   Mar
    2019   Apr
    Can someone help me code this?
    Thanks.

  • #2
    Perhaps this will start you in a useful direction.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input int year str3 month
    2006 "Jan"
    2006 "Feb"
    2006 "Mar"
    2006 "Apr"
    2006 "May"
    2006 "Jun"
    2006 "Jul"
    2006 "Aug"
    2006 "Sep"
    2006 "Oct"
    2006 "Nov"
    2006 "Dec"
    2007 "Jan"
    2007 "Feb"
    2007 "Mar"
    2007 "Apr"
    2007 "May"
    2007 "Jun"
    2007 "Jul"
    end
    
    generate fy = monthly(string(year)+month,"YM")
    format fy %tmCCYY-NN
    Code:
    . list, noobs sep(6)
    
      +------------------------+
      | year   month        fy |
      |------------------------|
      | 2006     Jan   2006-01 |
      | 2006     Feb   2006-02 |
      | 2006     Mar   2006-03 |
      | 2006     Apr   2006-04 |
      | 2006     May   2006-05 |
      | 2006     Jun   2006-06 |
      |------------------------|
      | 2006     Jul   2006-07 |
      | 2006     Aug   2006-08 |
      | 2006     Sep   2006-09 |
      | 2006     Oct   2006-10 |
      | 2006     Nov   2006-11 |
      | 2006     Dec   2006-12 |
      |------------------------|
      | 2007     Jan   2007-01 |
      | 2007     Feb   2007-02 |
      | 2007     Mar   2007-03 |
      | 2007     Apr   2007-04 |
      | 2007     May   2007-05 |
      | 2007     Jun   2007-06 |
      |------------------------|
      | 2007     Jul   2007-07 |
      +------------------------+

    Comment


    • #3
      I'm not sure when your fiscal year starts, this is in case it starts in July.

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input int year str3 month
      2006 "Jan"
      2006 "Feb"
      2006 "Mar"
      2006 "Apr"
      2006 "May"
      2006 "Jun"
      2006 "Jul"
      2006 "Aug"
      2006 "Sep"
      2006 "Oct"
      2006 "Nov"
      2006 "Dec"
      2007 "Jan"
      2007 "Feb"
      2007 "Mar"
      2007 "Apr"
      2007 "May"
      2007 "Jun"
      2007 "Jul"
      2007 "Aug"
      2007 "Sep"
      2007 "Oct"
      end
      
      gen year1 = year-1 if month=="Jan" | month=="Feb" | month=="Mar" | month=="Apr" | month=="May" | month=="Jun"
      replace year1 = year if month=="Jul" | month=="Aug" | month=="Sep" | month=="Oct" | month=="Nov" | month=="Dec"
      gen str3 year_short = "-" + string(year1-1999, "%02.0f")
      gen fiscal_year = string(year1) + year_short
      drop year1 year_short
      list, abbreviate(12)
      Code:
           +----------------------------+
           | year   month   fiscal_year |
           |----------------------------|
        1. | 2006     Jan       2005-06 |
        2. | 2006     Feb       2005-06 |
        3. | 2006     Mar       2005-06 |
        4. | 2006     Apr       2005-06 |
        5. | 2006     May       2005-06 |
           |----------------------------|
        6. | 2006     Jun       2005-06 |
        7. | 2006     Jul       2006-07 |
        8. | 2006     Aug       2006-07 |
        9. | 2006     Sep       2006-07 |
       10. | 2006     Oct       2006-07 |
           |----------------------------|
       11. | 2006     Nov       2006-07 |
       12. | 2006     Dec       2006-07 |
       13. | 2007     Jan       2006-07 |
       14. | 2007     Feb       2006-07 |
       15. | 2007     Mar       2006-07 |
           |----------------------------|
       16. | 2007     Apr       2006-07 |
       17. | 2007     May       2006-07 |
       18. | 2007     Jun       2006-07 |
       19. | 2007     Jul       2007-08 |
       20. | 2007     Aug       2007-08 |
           |----------------------------|
       21. | 2007     Sep       2007-08 |
       22. | 2007     Oct       2007-08 |
           +----------------------------+

      Comment


      • #4
        Thank you so much. Our fiscal years start from April and goes to March. So a slight modification to your code as below worked. If I had data prior to 2000 this may not have worked I guess.
        Wondering if there are more straight forward solutions.

        Code:
        gen year1 = year-1 if month=="Jan" | month=="Feb" | month=="Mar"
        replace year1 = year if  month=="Apr" | month=="May" | month=="Jun" | month=="Jul" | month=="Aug" | ///
                                             month=="Sep" | month=="Oct" | month=="Nov" | month=="Dec" 
        gen str3 year_short = "-" + string(year1-1999, "%02.0f")
        gen fiscal_year = string(year1) + year_short
        drop year1 year_short



        Originally posted by Wouter Wakker
        I'm not sure when your fiscal year starts, this is in case it starts in July.

        Code:
        * Example generated by -dataex-. To install: ssc install dataex
        clear
        input int year str3 month
        2006 "Jan"
        2006 "Feb"
        2006 "Mar"
        2006 "Apr"
        2006 "May"
        2006 "Jun"
        2006 "Jul"
        2006 "Aug"
        2006 "Sep"
        2006 "Oct"
        2006 "Nov"
        2006 "Dec"
        2007 "Jan"
        2007 "Feb"
        2007 "Mar"
        2007 "Apr"
        2007 "May"
        2007 "Jun"
        2007 "Jul"
        2007 "Aug"
        2007 "Sep"
        2007 "Oct"
        end
        
        gen year1 = year-1 if month=="Jan" | month=="Feb" | month=="Mar" | month=="Apr" | month=="May" | month=="Jun"
        replace year1 = year if month=="Jul" | month=="Aug" | month=="Sep" | month=="Oct" | month=="Nov" | month=="Dec"
        gen str3 year_short = "-" + string(year1-1999, "%02.0f")
        gen fiscal_year = string(year1) + year_short
        drop year1 year_short
        list, abbreviate(12)
        Code:
         +----------------------------+
        | year month fiscal_year |
        |----------------------------|
        1. | 2006 Jan 2005-06 |
        2. | 2006 Feb 2005-06 |
        3. | 2006 Mar 2005-06 |
        4. | 2006 Apr 2005-06 |
        5. | 2006 May 2005-06 |
        |----------------------------|
        6. | 2006 Jun 2005-06 |
        7. | 2006 Jul 2006-07 |
        8. | 2006 Aug 2006-07 |
        9. | 2006 Sep 2006-07 |
        10. | 2006 Oct 2006-07 |
        |----------------------------|
        11. | 2006 Nov 2006-07 |
        12. | 2006 Dec 2006-07 |
        13. | 2007 Jan 2006-07 |
        14. | 2007 Feb 2006-07 |
        15. | 2007 Mar 2006-07 |
        |----------------------------|
        16. | 2007 Apr 2006-07 |
        17. | 2007 May 2006-07 |
        18. | 2007 Jun 2006-07 |
        19. | 2007 Jul 2007-08 |
        20. | 2007 Aug 2007-08 |
        |----------------------------|
        21. | 2007 Sep 2007-08 |
        22. | 2007 Oct 2007-08 |
        +----------------------------+

        Comment


        • #5
          #1 lacked a clear data example using dataex. If this were my problem I would prefer a numeric variable with value labels. So, in addition to excellent advice from William and Wouter, here is another way to do it.

          Code:
          clear 
          input year   str3 month
          2006    Jan
          2006    Feb
          2006    Mar
          2006    Apr
          2006    May
          2006    Jun
          2006    Jul
          2006    Aug
          2006    Sep
          2006    Oct
          2006    Nov
          2006    Dec
          2007    Jan
          2007    Feb
          2007    Mar
          2007    Apr
          2007    May
          2007    Jun
          2007    Jul
          2019   Mar
          2019   Apr
          end 
          
          gen mdate = monthly(month + string(year), "MY") 
          
          format mdate %tm
          
          gen fyear = year(dofm(mdate - 3)) 
          
          forval y = 2004/2019 { 
              local Y : di %02.0f mod(`y' + 1, 100) 
              label def fyear `y' "`y'-`Y'", modify 
          }
          
          label val fyear fyear 
               +----------------------------------+
               | year   month     mdate     fyear |
               |----------------------------------|
            1. | 2006     Jan    2006m1   2005-06 |
            2. | 2006     Feb    2006m2   2005-06 |
            3. | 2006     Mar    2006m3   2005-06 |
               |----------------------------------|
            4. | 2006     Apr    2006m4   2006-07 |
            5. | 2006     May    2006m5   2006-07 |
            6. | 2006     Jun    2006m6   2006-07 |
            7. | 2006     Jul    2006m7   2006-07 |
            8. | 2006     Aug    2006m8   2006-07 |
            9. | 2006     Sep    2006m9   2006-07 |
           10. | 2006     Oct   2006m10   2006-07 |
           11. | 2006     Nov   2006m11   2006-07 |
           12. | 2006     Dec   2006m12   2006-07 |
           13. | 2007     Jan    2007m1   2006-07 |
           14. | 2007     Feb    2007m2   2006-07 |
           15. | 2007     Mar    2007m3   2006-07 |
               |----------------------------------|
           16. | 2007     Apr    2007m4   2007-08 |
           17. | 2007     May    2007m5   2007-08 |
           18. | 2007     Jun    2007m6   2007-08 |
           19. | 2007     Jul    2007m7   2007-08 |
               |----------------------------------|
           20. | 2019     Mar    2019m3   2018-19 |
               |----------------------------------|
           21. | 2019     Apr    2019m4   2019-20 |
               +----------------------------------+
          This problem has unsurprisingly arisen many times before. For example, a function ncyear() in egenmore on SSC goes back to 2000 at least, while there is a more systematic discussion in https://www.stata-journal.com/articl...article=st0394

          Shifting the origin by so many months is a trick used by Clyde Schechter.

          Comment


          • #6
            Thank you Nick! Your solution worked like a charm. Thank you also for the link to the Stata journal article. Quite informative.

            Will be mindful of clearer data example with dataex next time.

            Thanks,
            Rijo.

            Originally posted by Nick Cox
            #1 lacked a clear data example using dataex. If this were my problem I would prefer a numeric variable with value labels. So, in addition to excellent advice from William and Wouter, here is another way to do it.

            Code:
            clear
            input year str3 month
            2006 Jan
            2006 Feb
            2006 Mar
            2006 Apr
            2006 May
            2006 Jun
            2006 Jul
            2006 Aug
            2006 Sep
            2006 Oct
            2006 Nov
            2006 Dec
            2007 Jan
            2007 Feb
            2007 Mar
            2007 Apr
            2007 May
            2007 Jun
            2007 Jul
            2019 Mar
            2019 Apr
            end
            
            gen mdate = monthly(month + string(year), "MY")
            
            format mdate %tm
            
            gen fyear = year(dofm(mdate - 3))
            
            forval y = 2004/2019 {
            local Y : di %02.0f mod(`y' + 1, 100)
            label def fyear `y' "`y'-`Y'", modify
            }
            
            label val fyear fyear
            +----------------------------------+
            | year month mdate fyear |
            |----------------------------------|
            1. | 2006 Jan 2006m1 2005-06 |
            2. | 2006 Feb 2006m2 2005-06 |
            3. | 2006 Mar 2006m3 2005-06 |
            |----------------------------------|
            4. | 2006 Apr 2006m4 2006-07 |
            5. | 2006 May 2006m5 2006-07 |
            6. | 2006 Jun 2006m6 2006-07 |
            7. | 2006 Jul 2006m7 2006-07 |
            8. | 2006 Aug 2006m8 2006-07 |
            9. | 2006 Sep 2006m9 2006-07 |
            10. | 2006 Oct 2006m10 2006-07 |
            11. | 2006 Nov 2006m11 2006-07 |
            12. | 2006 Dec 2006m12 2006-07 |
            13. | 2007 Jan 2007m1 2006-07 |
            14. | 2007 Feb 2007m2 2006-07 |
            15. | 2007 Mar 2007m3 2006-07 |
            |----------------------------------|
            16. | 2007 Apr 2007m4 2007-08 |
            17. | 2007 May 2007m5 2007-08 |
            18. | 2007 Jun 2007m6 2007-08 |
            19. | 2007 Jul 2007m7 2007-08 |
            |----------------------------------|
            20. | 2019 Mar 2019m3 2018-19 |
            |----------------------------------|
            21. | 2019 Apr 2019m4 2019-20 |
            +----------------------------------+
            This problem has unsurprisingly arisen many times before. For example, a function ncyear() in egenmore on SSC goes back to 2000 at least, while there is a more systematic discussion in https://www.stata-journal.com/articl...article=st0394

            Shifting the origin by so many months is a trick used by Clyde Schechter.

            Comment

            Working...
            X