Transforming Calendar Quarter to Financial Quarter

Question

I am working with a dateframe (INPUT) that contains number the of transaction of a product per calendar quarter. The first column (DATE) contains the calendar quarter in this format "2016 Q2". I would like to transform this date into the a financial quarter format such as "2016/17 Q1". The financial year start in the 1st April.

I came up with the following code which does the job, but I was wondering if there is a formula or a neater code that I could use.

INPUT$FY_Date=character(nrow(INPUT))

for (i in 1:nrow(INPUT)) {
    INPUT$FY_Date[i]= if(substr(INPUT$DATE[i],7,7)==1)  paste(as.numeric(substr(INPUT$DATE[i],1,4))-1,"/",substr(INPUT$DATE[i],3,4)," Q4",sep="") else 

    paste(substr(INPUT$DATE[i],1,4),"/",  formatC(as.numeric(substr(INPUT$DATE[i],3,4))+1,width=2,format="d",flag=0)," Q",as.numeric(substr(INPUT$DATE[i],7,7))-1,sep="")
}     

I could not find any previous related posts so I would appreciate any guidance.

Answer 1

Using the "yearqtr" class defined in zoo we can do it in two lines of code.

Convert to "yearqtr". The "yearqtr" class uses an internal representation of year + (qtr-1)/4 where qtr is 1, 2, 3 or 4 so adding 3/4 will shift it to the year-end year and fiscal quarter. Then in the final line of code as.integer will extract the year-end year. format function can be used to get the rest where %y means 2 digit year and %q means quarter.

library(zoo)

# test input
yq <- c("2016 Q2", "2016 Q3", "2016 Q4", "2017 Q1")

fyq <- as.yearqtr(yq, format = "%Y Q%q") + 3/4
paste0(as.integer(fyq) - 1, format(fyq, "/%y Q%q"))

giving:

[1] "2016/17 Q1" "2016/17 Q2" "2016/17 Q3" "2016/17 Q4"

Note that if you don't need the specific format shown in the question you could just use format(fyq) in place of the last line or maybe format(fyq, "%Y Q%q").

Update: Minor code improvements.