In Excel, can I get historical data across the columns instead of down the rows?
Note that each data feed on Nasdaq Data Link is delivered through one of the following Nasdaq Data Link APIs:
- Streaming API for real-time data
- REST API for real-time or delayed data
- REST API for time-series
- REST API for tables
You can learn more about these APIs and data formats here.
The Excel Add-In may only be used with data delivered through the REST API for time-series or the REST API for tables.
When downloading data that is in time-series format, it is possible to get historical data across columns instead of down the rows by using the QSERIES formula or the Nasdaq Data Link Formula Builder.
However, when downloading data that is in tables format, it is not possible to get historical data across columns instead of down the rows. For tables, you would have to download the data and rearrange the data on your own after the data has been downloaded.
Using the QSERIES formula
Note that the structure of the QSERIES formula is as follows, with transpose as the ninth argument:
=QSERIES(quandlCode, dateRange, frequency, sortOrder, transformation, limit, headers, dates, transpose)
To return historical data across columns, put TRUE for the transpose argument, like this:
=QSERIES("XNAS/ACIW",,,,,,,,TRUE)
The above formula will return data across columns for the XNAS/ACIW time-series.
Using the Nasdaq Data Link Formula Builder
To download data across columns by using the Nasdaq Data Link Formula Builder, please follow these steps:
- Open Excel
- Go to the Nasdaq Data Link tab and click Get Data
- Search for the time-series data feed you want or enter the database code for the data feed you want. For example, enter XNAS for the NASDAQ Stock Market Prices data feed, like this:
- Click Next and follow the prompts. When you reach the Placement stage, check off "Show dates across columns" like this:
- Click Insert to download the data.
