Excel: Calling Data by Date and Time

Thom Hartle – June 12, 2025

A typical historical Excel RTD dashboard pulls in market data using the bar index parameter. Below, the bar index is in column A. The most current bar is "0". The previous bar is "-1", etc.

f1

However, you may want to start with a specific date and time for pulling in data. Excel's VLOOKUP function is used.

First, a reference table for date and time along with the bar index is used, which is columns P and Q.

f2

The date and time is copied and pasted as a Value into cell I2:

6/12/2025  7:30:00 AM

And, cell I1 has the VLOOKUP function:

=VLOOKUP(I2,P2:Q5000,2,FALSE)

f3

The VLOOKUP function is finding the date and time in column P and is returning the bar index from Column C to cell I1, which is linked to cell A2.

Requirements: CQG Integrated Client or QTrader, and Excel 2016 (locally installed, not in the Cloud) or more recent.

Downloads

Disclaimer

Trading and investment carry a high level of risk, and CQG, Inc. does not make any recommendations for buying or selling any financial instruments. We offer educational information on ways to use our sophisticated CQG trading tools, but it is up to our customers and other readers to make their own trading and investment decisions or to consult with a registered investment advisor. The opinions expressed here are solely those of the author and do not reflect the opinions of CQG, Inc. or its affiliates.