if/else statement - correct application of these functions

[Alberto Alvarez]

Dear Stata Users,


I have the end of the fiscal year month (the dataset is monthly). My final goal is to construct a date variable that will have a fiscal year and fiscal quarter. First, I define a fiscal year:

bys permno: replace fyear = fyear - 1 if month(date)<fyr

Then, I want to label months (like 1^st, 2^nd, 3^rd) in a fiscal year. My idea is to use something like this:

{
if month == fyr {
       replace month = 1
       }
      else if fyr<month {
            replace month =month- fyr
           }
          else if fyr>month {
             replace month =month + fyr
                }
}
​​​​​​​

However, this code does not allocates month within the fiscal year. Can you, please help me with this issue.
\

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date double fyear float month int mdate double fyr
54594 13695 1997  6 449  5
54594 13726 1997  7 450  5
54594 13573 1997  2 445  5
54594 13818 1997 10 453  5
54594 13604 1997  3 446  5
54594 13846 1997 11 454  5
54594 13879 1997 12 455  5
54594 13755 1997  8 451  5
54594 13634 1997  4 447  5
54594 13664 1997  5 448  5
54594 13545 1997  1 444  5
54594 13787 1997  9 452  5
54594 13969 1998  3 458  5
54594 14028 1998  5 460  5
54594 14244 1998 12 467  5
54594 14213 1998 11 466  5
54594 14060 1998  6 461  5
54594 14182 1998 10 465  5
54594 14152 1998  9 464  5
54594 14122 1998  8 463  5
54594 13999 1998  4 459  5
54594 13937 1998  2 457  5
54594 13909 1998  1 456  5
54594 14091 1998  7 462  5
54594 14487 1999  8 475  5
54594 14517 1999  9 476  5
54594 14425 1999  6 473  5
54594 14578 1999 11 478  5
54594 14455 1999  7 474  5
54594 14334 1999  3 470  5
54594 14609 1999 12 479  5
54594 14392 1999  5 472  5
54594 14273 1999  1 468  5
54594 14546 1999 10 477  5
54594 14364 1999  4 471  5
54594 14301 1999  2 469  5
54594 14669 2000  2 481  5
54594 14973 2000 12 491  5
54594 14853 2000  8 487  5
54594 14640 2000  1 480  5
54594 14882 2000  9 488  5
54594 14914 2000 10 489  5
54594 14822 2000  7 486  5
54594 14761 2000  5 484  5
54594 14728 2000  4 483  5
54594 14791 2000  6 485  5
54594 14944 2000 11 490  5
54594 14700 2000  3 482  5
54594 15187 2001  7 498  5
54594 15309 2001 11 502  5
54594 15155 2001  6 497  5
54594 15064 2001  3 494  5
54594 15279 2001 10 501  5
54594 15006 2001  1 492  5
54594 15034 2001  2 493  5
54594 15218 2001  8 499  5
54594 15126 2001  5 496  5
54594 15095 2001  4 495  5
54594 15246 2001  9 500  5
54594 15340 2001 12 503  5
54594 15427 2002  3 506  5
54594 15371 2002  1 504  5
54594 15460 2002  4 507  5
54594 15582 2002  8 511  5
54594 15705 2002 12 515  5
54594 15552 2002  7 510  5
54594 15491 2002  5 508  5
54594 15644 2002 10 513  5
54594 15613 2002  9 512  5
54594 15399 2002  2 505  5
54594 15519 2002  6 509  5
54594 15673 2002 11 514  5
54594 16037 2003 11 526  5
54594 15917 2003  7 522  5
54594 15736 2003  1 516  5
54594 15855 2003  5 520  5
54594 16070 2003 12 527  5
54594 15886 2003  6 521  5
54594 15764 2003  2 517  5
54594 15825 2003  4 519  5
54594 15978 2003  9 524  5
54594 16009 2003 10 525  5
54594 15795 2003  3 518  5
54594 15946 2003  8 523  5
50906 14244 1998 12 467 10
50906 14091 1998  7 462 10
50906 14028 1998  5 460 10
50906 13937 1998  2 457 10
50906 13909 1998  1 456 10
50906 13999 1998  4 459 10
50906 14213 1998 11 466 10
50906 14060 1998  6 461 10
50906 14152 1998  9 464 10
50906 14122 1998  8 463 10
50906 14182 1998 10 465 10
50906 13969 1998  3 458 10
50906 14455 1999  7 474 10
50906 14334 1999  3 470 10
50906 14392 1999  5 472 10
50906 14301 1999  2 469 10
end
format %d date
format %tm mdate

[Jean-Claude Arbaut]

I am not sure I understand. You have dates, and you want to compute the fiscal year and order of the month (1-12, but not necessarily jan-dec) in the fiscal year, right?

Does the fiscal year span alsways the same period (then, which one, as it varies across countries), or have you a variable indicating the fiscal year start or end (I would say fyr, but it's not very clear, as it takes values 5 and 10 for the same year)?

[Alberto Alvarez]

Thank you for the reply. (permno is a firm identifier) What I want is to calculate the date, say "fdate" that will contain fiscal year and a fiscal quarter. I have a year (fyear) and the month when the fiscal year ends (fyr). Given this, I can calculate fiscal year by:

 
 bys permno: replace fyear = fyear - 1 if month(date)<fyr

What I want know, is to calculate a fiscal month by the using the idea in the code above. The reason why I changing month is that when I take into account fiscal month, the listing of months (1^st, 2^nd, etc) is going to change, depending on the beginning month of the fiscal year.

Fiscal year should span 12 months, but there are some observations with missing months

[Nick Cox]

The if command operates quite differently from the if qualifier. The condition is evaluated only once (if variables are named, their values in the first observation are used); most of all, there is no implied loop over observations.

This is explained, if rather a backwards manner, in e.g. the FAQ https://www.stata.com/support/faqs/p...-if-qualifier/

Like Jean-Claude I don't understand all the details in the question (what are 5 and 10???) but I think I understand that you want a mapping from calendar years to fiscal years.

You are alluding to code that you do not show us and implying that it doesn't work without explaining what happens. This has no more diagnostic value than "I feel unwell".

Specifically: You don't need a loop here. The translation (ordinary sense) is just a translation (mathematical sense) of the origin followed by simple calculations.

Technique is best expressed by example:

Let's suppose that the fiscal year starts in April, month 4. If you need a different month, just change the code accordingly. If the fiscal year 1998-1999 is better labelled 1999 not 1998, just add 1 to the years.

For much more, see https://www.stata-journal.com/articl...article=st0394 although the devices here don't need that paper to be understood.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int mdate
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
end
format %tm mdate

. gen fyrdate = year(dofm(mdate - 3))

. gen fyrmonth = month(dofm(mdate - 3))

. gen fyrquarter = ceil(fyrmonth/3)

. list, sepby(fyrdate fyrquarter)

     +-----------------------------------------+
     |   mdate   fyrdate   fyrmonth   fyrqua~r |
     |-----------------------------------------|
  1. |  1997m1      1996         10          4 |
  2. |  1997m2      1996         11          4 |
  3. |  1997m3      1996         12          4 |
     |-----------------------------------------|
  4. |  1997m4      1997          1          1 |
  5. |  1997m5      1997          2          1 |
  6. |  1997m6      1997          3          1 |
     |-----------------------------------------|
  7. |  1997m7      1997          4          2 |
  8. |  1997m8      1997          5          2 |
  9. |  1997m9      1997          6          2 |
     |-----------------------------------------|
 10. | 1997m10      1997          7          3 |
 11. | 1997m11      1997          8          3 |
 12. | 1997m12      1997          9          3 |
     |-----------------------------------------|
 13. |  1998m1      1997         10          4 |
 14. |  1998m2      1997         11          4 |
 15. |  1998m3      1997         12          4 |
     |-----------------------------------------|

EDIT: If the fiscal year starts in different months for different enterprises, then just replace 3 with the name of a variable containing the start month of the fiscal year. In your case that would appear to mean

gen fyrdate = year(dofm(mdate - fyr - 1))

. gen fyrmonth = month(dofm(mdate - fyr - 1))

. gen fyrquarter = ceil(fyrmonth/3)

3 remains as before as it is just 3 months = 1 quarter.

[Jean-Claude Arbaut]

What is not clear yet is:
* fyear is the year of what? You are replacing it, but we don't know what it's supposed to hold in the first place.
* date is the date of what? I'll assume you want to convert this date to a fiscal year/quarter pair, with the knowledge that fyr is the end of the fiscal year (that is, fyr is the last month of the fiscal year).

If I interpret your question correctly, then you only need date and fyr, and you can do this:

gen fiscal_year=year(date)-(month(date)<=fyr)
gen fiscal_month=mod(month(date)+11-fyr,12)+1
gen fiscal_quarter=floor(mod(month(date)+11-fyr,12)/3)+1

The fiscal year is the year of date, unless the month is less than or equal to fyr, then it's the preceding fiscal year.
The fiscal "month" is adjusted to be 1-12 during the fiscal year (that is, month 1 is the first month of the fiscal year, and it is june if fyr=5, since may is the last of the preceding fiscal year). This variable is not necessary, but it may help to understand how the next formula works.
The fiscal quarter is just the fiscal month converted to a quarter (month 1-3 is first quarter, etc.)

Hope this helps

Jean-Claude Arbaut