How to calculate adjusted prices

In addition to unadjusted prices (O/H/L/C/Vo), the data includes the adjustment factor AdjFactor to reflect stock splits/reverse splits (e.g., for a 1:2 stock split, AdjFactor = 0.5 on the ex-date).

This page explains, for spreadsheet users, how to calculate adjusted prices (e.g., adjusted close) yourself using AdjFactor.

Assumptions (what “adjusted” means here)

• Goal: Remove artificial jumps caused by stock splits/reverse splits so the time series is easier to compare.
• Scope: Adjustments in this API cover stock splits/reverse splits only (some corporate actions such as dividends are not covered).

Concept

AdjFactor is the factor populated on the ex-date (the effective date of a split/reverse split). To adjust historical prices, you need to accumulate (multiply) the AdjFactor values that appear on later (more recent) dates.

In other words, older dates should reflect more subsequent split/reverse split events, so you first create a cumulative adjustment factor (CumAdj) and then apply it to prices.

Steps in a spreadsheet (with a table example)

1) Prepare the required columns from the downloaded file

At minimum, you need these columns:

• Date
• C (unadjusted close) — the same logic applies to O/H/L
• Vo (unadjusted volume) — if you also want adjusted volume
• AdjFactor (adjustment factor)

2) Sort by date in descending order (newest first)

This is the most important point. Sort Date in descending order so you can compute CumAdj from top to bottom.

3) Create the cumulative adjustment factor CumAdj

Create a table like the following (this example assumes a single 1:2 split occurs once in the period).

Row	A:Date	B:C (unadjusted)	C:Vo (unadjusted)	D:AdjFactor	E:CumAdj (cumulative)	F:AdjC (calculated)	G:AdjVo (calculated)
2	2024-01-12	500	1,200,000	1.0
3	2024-01-11	480	2,400,000	0.5
4	2024-01-10	980	1,100,000	1.0

Define CumAdj as “the product of all AdjFactor values on dates newer than the current row”. (Because an ex-date factor should be applied to dates before that ex-date, it effectively impacts the next row down (older date) in this top-to-bottom calculation.)

Cell formulas (example)

• E2 (latest date): 1
• E3 and below (fill down): “previous (newer) CumAdj” × “previous (newer) AdjFactor”
  • Formula in E3: =E2*D2
  • Copy this down to the last row

In this example, AdjFactor = 0.5 on the ex-date 2024-01-11 is reflected in CumAdj for the older date 2024-01-10, resulting in 0.5.

Row	A:Date	B:C (unadjusted)	C:Vo (unadjusted)	D:AdjFactor	E:CumAdj (cumulative)	F:AdjC (calculated)	G:AdjVo (calculated)
2	2024-01-12	500	1,200,000	1.0	1.0
3	2024-01-11	480	2,400,000	0.5	1.0
4	2024-01-10	980	1,100,000	1.0	0.5

4) Calculate adjusted close (example: AdjC)

For prices (O/H/L/C), calculate unadjusted price × CumAdj.

• F2 (fill down):
  • =B2*E2

Row	A:Date	B:C (unadjusted)	C:Vo (unadjusted)	D:AdjFactor	E:CumAdj (cumulative)	F:AdjC (calculated)	G:AdjVo (calculated)
2	2024-01-12	500	1,200,000	1.0	1.0	500.00
3	2024-01-11	480	2,400,000	0.5	1.0	480.00
4	2024-01-10	980	1,100,000	1.0	0.5	490.00

5) Calculate adjusted volume (example: AdjVo) (optional)

Volume is the inverse of price. For example, to keep continuity under a 1:2 split (doubling pre-split volume), calculate unadjusted volume ÷ CumAdj.

• G2 (fill down):
  • =C2/E2

CumAdj is not normally zero, but for safety you can use something like =IF(E2=0,"",C2/E2) in Excel.

Row	A:Date	B:C (unadjusted)	C:Vo (unadjusted)	D:AdjFactor	E:CumAdj (cumulative)	F:AdjC (calculated)	G:AdjVo (calculated)
2	2024-01-12	500	1,200,000	1.0	1.0	500.00	1,200,000
3	2024-01-11	480	2,400,000	0.5	1.0	480.00	2,400,000
4	2024-01-10	980	1,100,000	1.0	0.5	490.00	2,200,000