Years, ranges, and relative periods

SheetsFinance provides a number of ways to access annual and quarterly/bi-annual data. In this article we'll cover relative periods (LY and LQ) as well as switching from fiscal years to calendar years with calYear.

Single years and ranges

Every function that takes a year accepts a single year, a dash or colon range, or relative periods such as LY and LQ. Relative periods are resolved against the data the function returns, so they always point at real reporting periods rather than fixed calendar dates.

Enter the year argument as a quoted string in one of these forms:

• Single year: "2020".

• Dash range: "2015-2020".

• Colon range: "2023:2024". This works the same as "2023-2024", and colon ranges must be in quotes so the spreadsheet does not read them as a cell range.

• Mixed ranges: "2020:LY" and "LQ-7:LQ" are valid, since both ends can be a year or a relative period.

Relative periods

Ranges use a colon, for example "LY-4:LY", "LQ-7:LQ" or "2020:LY". LY-4:LY returns five years and LQ-7:LQ returns eight periods.

Worked examples

Each formula selects its periods against the data the function returns, so the range follows new filings without you editing the year.

• =SF("AAPL", "income", "all", "LY-4:LY") returns the last five fiscal years of annual income data.

• =SF("AAPL", "incomeQ", "all", "LQ-4:LQ") returns the last five quarters: on incomeQ, LQ counts quarters.

• =SF("AAPL", "income", "all", "2020:LY") mixes a fixed year with a relative end point: fiscal 2020 through the latest year with data for the request.

• =SF("AAPL", "incomeQ2", "all", "LQ-1") returns Q2 of the previous fiscal year, because a single-quarter variant filters to Q2 before applying the offset.

• =SF("AAPL", "incomeQ", "all", "LQ") returns just the latest available quarter.

How LY resolves

LY is the latest year with data for the request, anchored to the newest period the function returns. On all-quarter functions LY can be a year still in progress. On single-quarter variants (Q1, Q2, Q3, Q4), LY is the latest year that has that quarter.

How LQ resolves

LQ steps through the periods the function returns, so it counts a different unit depending on the function:

• All-quarter and TTM variants: LQ counts quarters, so "LQ-7:LQ" spans eight quarters.

• Annual variants: LQ counts years.

• Single-quarter variants (Q1 to Q4): LQ counts the years that have the requested fiscal quarter, so "LQ-1" is that quarter in the previous fiscal year.

• Earnings, ESG and insider statistics: LQ counts quarters, and earnings counts from the latest reported quarter.

• Insider transactions, analyst grades and analyst ratings: only LY forms select years; LQ uses the default window.

Forward periods and estimates

Estimates and earnings also accept forward offsets such as "LY+1" and "LQ+1" to reach estimated future periods. On quarterly estimates, leaving the year blank returns the next upcoming quarter; annual estimates return the next fiscal year. See Analyst Estimates.

Calendar alignment

The "calYear" option switches the both the search and output from fiscal periods to calendar periods: each fiscal quarter maps to the nearest calendar quarter, and a fiscal year ending January to May counts as the previous calendar year. Period headers keep the Q2-2023 and FY-2023 format but then refer to calendar quarters and years. You can use calYear on the financial statement, ratio, growth, revenue segmentation, and KPI functions. See Key Ratios and Financial Statements.

=SF("AAPL", "incomeQ", "all", "2019-2020", "calYear")

How fiscal quarters get attributed to calendar quarters

Each fiscal quarter is attributed to the calendar quarter whose end date (March 31, June 30, September 30, or December 31) is nearest to the fiscal quarter's own end date. For a company whose fiscal year ends in June, the fiscal quarters map like this:

How fiscal years get attributed to calendar years

A fiscal year ending January through May counts as the previous calendar year, and a fiscal year ending June through December counts as the calendar year in which it ends. A fiscal year ending June 30, 2020 (covering July 2019 to June 2020) is labeled FY-2020, while a fiscal year ending January 31, 2020 is labeled FY-2019.

With calYear, LY and LQ count calendar years and calendar quarters, so relative ranges such as "LY-4:LY" and "LQ-7:LQ" select calendar periods.