Excel STOCKHISTORY - volume not being adjusted correctly for splits on QQQ and TQQQ

Hi, Dan Myers

Welcome to Microsoft Q&A forum.

Thanks for your question. Excel’s STOCKHISTORY is returning prices that are adjusted for splits, but its volume column appears to be un-adjusted/raw (i.e. the number of shares actually traded on that historical date, in the share units of the time). Yahoo/Investing.com are showing volume adjusted to the current share unit (so historical volumes are multiplied by the split ratio).

Evidence from your screenshot: 69,185,586 (STOCKHISTORY on 2022-01-12) vs ≈138,371,200 (Yahoo), that’s ~×2, so the split factor for 2022-01-13 in your example is 2.0 and Yahoo has adjusted earlier volumes accordingly.

In short: STOCKHISTORY is giving you raw historical volumes; the other sites are giving volume adjusted to present share count.

How to fix it in Excel

Create a small “Splits” table with each split date and the split factor (e.g. for a 2-for-1 split the factor is 2, for a 3-for-1 it’s 3, for a 1-for-4 reverse split the factor is 0.25, etc.). Then multiply each historical row’s volume by the product of all split factors that happened after that row’s date.

Example layout:

• A = Date (from STOCKHISTORY)

• B = Volume (from STOCKHISTORY — unadjusted)

• F = SplitDate (your split table)

• G = Factor (e.g. 2 for a 2-for-1 split on that SplitDate)

Formula (modern Excel with FILTER / dynamic arrays). Put in C2 for adjusted volume and copy down:

= B2 * IFERROR( PRODUCT( FILTER( $G$2:$G$10, $F$2:$F$10 > A2 ) ), 1 )

Explanation: for the date in A2 it finds all split factors whose SplitDate is later than A2, takes the PRODUCT of those factors (if none, IFERROR(...,1) gives 1) and multiplies B2 by that product.

If you don’t have FILTER (older Excel), use an array formula (enter with Ctrl+Shift+Enter):

= B2 * PRODUCT( IF( $F$2:$F$10 > A2, $G$2:$G$10, 1 ) )

Hope this helps. Feel free to get back if you have other questions.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.