S&P Cap IQ Pro: SPGRangeV Function Support for Financial Data
What is the SPGRangeV function?
The SPGRangeV funciton is available within the S&P Global Excel Add-in and allows you to
pull a time-series range of values with a single formula.
The basic syntax for the formula is: =SPGRANGEV("IDENTIFIER","DATA ITEM", "PERIOD")
The following formula: =SPGRANGEV("WFC", "SP_TOTAL_ASSETS", "FY-4") will return the
most current year’s reported total assets for Wells Fargo as well as the preceding four fiscal
years.
Total Assets =SPGRANGEV("NYSE:WFC",
1,952,911,000 "SP_TOTAL_ASSETS", "FY-4")
1,927,555,000
1,895,883,000
1,951,757,000
1,930,115,000
How do I build an SPGRANGEV function from scratch?
The Market Intelligence Formula Builder is used to create the SPGRANGEV function. If the
data item you select is able to be pulled as a range of values, the first line of the Parameters
section will show a drop down that defaults to “Single Value”. By clicking on that drop down
and selecting “Range of Values” you will change the output formula to SPGRANGEV.
Additional Info:
Optional Parameters exist for metrics and can be customized using the Advanced Options
area of the Formula Builder. Below is an example of a formula with some of these optional
parameters changed from their default.
=SPGRANGEV("WFC", "SP_TOTAL_ASSETS", "FY-4", "Options: Curr=EUR, Mag=6,
Dates=Before")
This example has changed the default currency to Euros (Curr=EUR), the magnitude to
millions (Mag=6), and the end dates of each period showing before the data
(Dates=Before)
Total Assets
12/31/2020 1,596,505
12/31/2019 1,717,644
12/31/2018 1,655,864
12/31/2017 1,625,423
12/31/2016 1,830,135
S&P Cap IQ Pro: SPGRangeV Function Support for Market Data
Basic SPG Market Data formula (single-cell):
=SPG("IDENTIFIER", "DATA ITEM", Date)
=SPG("NYSE:BAC", "SP_PRICE_CLOSE", "12/31/2019")
Sample: 35.22
Advanced SPG Market Data formula (date range) - Actual Dates:
=SPGRangeV("IDENTIFIER", "DATA ITEM", Start Date, End Date, "Options: Curr, NA, Fill, Sort, Caption, Dates")
=SPGRangeV("NYSE:BAC", "SP_PRICE_CLOSE", "7/1/2019", "12/31/2019", "Options: Dates=Before, Fill=EOM")
Day Close Price
7/31/2019 30.68
8/31/2019 27.51
9/30/2019 29.17
10/31/2019 31.27
11/30/2019 33.32
12/31/2019 35.22
Advanced SPG Market Data formula (date range) - Relative Dates:
=SPGRangeV("IDENTIFIER", "DATA ITEM", Relative Date, End Date (optional), "Options: Dates, Fill, Sort, NA")
=SPGRangeV("NYSE:BAC", "SP_PRICE_CLOSE", "-12D", "Options: Dates=Before")
=SPGRangeV("NYSE:BAC", "SP_PRICE_CLOSE", "-12M", "12/31/2019", "Options: Dates=Before, Fill=EOM")
Day Close Price Day Close Price
7/26/2021 38.13 12/31/2018 24.64
7/27/2021 37.98 1/31/2019 28.47
7/28/2021 38.13 2/28/2019 29.08
7/29/2021 38.68 3/31/2019 27.59 in formula)
7/30/2021 38.36 4/30/2019 30.58
8/2/2021 37.96 5/31/2019 26.60
8/3/2021 38.55 6/30/2019 29.00
8/4/2021 38.27 7/31/2019 30.68
8/5/2021 39.02 8/31/2019 27.51 Relative Dates support for Days, Weeks,
9/30/2019 29.17 Months, Quarters and Years.
10/31/2019 31.27 #'s display in dropdown but can support
11/30/2019 33.32 -1 to -55000 (can change manually
12/31/2019 35.22
