CODEWITHSUNDEEP
vbaexcelexcel-2007
Nupur
Nupur

Reputation: 357

Calculating Fiscal Quarters

I need help with this code. Right now this is looking at calendar year quarters, but I would like to modify it to look at fiscal year quarters.

    Function test(time)
        Quarter = Format(time, "YYYYq")

        For vRow = 2 To 1000               
            report_time = Format(Sheets("data").Range("A" & vRow).Value, "YYYYq")

            Assessment = Sheets("data").Range("V" & vRow).Value  

            If (report_time = Quarter) Then
                denominator = denominator + 1
                If (Assessment= 1) Then
                    numerator = numerator + 1
                End If
            End If
        Next

        If denominator > 0 Then
            test = numerator / denominator
        Else
            test = 0
        End If            
    End Function

I would appreciate your time and suggestions regarding the same.

Upvotes: 0

Views: 912

Answers (1)

bonCodigo
bonCodigo

Reputation: 14361

Since I have more data to type and there's no enough space in the comments.

In terms of financial reporting you will have fiscal, calendar & actual. So please verify,

  • if you are working with any data vendor to get fiscal year data.
  • if you are planning to do any calendarization to the data? e.g. Turn fiscal year data into a calendar year data...etc
  • if you are going to compare fiscal year data of one company to another
  • Or you are trying to simply code a fiscal quarters from any fiscal year entered by a user without any calendarizing?

So if you want your code to be generic to all fiscal year/semi-annuals/quarters that a user chooses, then you need a pretty robus logic. ;) Because Apple US fiscal year that ends in September is not as same as BHP AU in June..

It's inevitable for fiscal year to have a life span from one calendar year to another if fiscal year Q4 doesn't tally with Calendar year Q4 which is December. Most painful level is when your fiscal quarters do not fall into a Calendar quarter. Where Fiscal year ends in odd months... E.g. WallMart US with January 31. Usually some European or British stocks seems to have this nature. So you have to validate each month in that case.

I did a calendarized tool sometime back. So if you can clarify your requirements clearly, happy to help.

Upvotes: 1

Related Questions