Can I convert daily time-series to monthly observations using the Excel Add-In?
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, data formats here.
Data that is delivered through the REST API for time-series or tables can be used with the Excel add-in.
However, when downloading data that is in tables format, it is not possible to change the frequency (e.g. daily to monthly). For tables, we recommend downloading the raw data and carrying out the required frequency transformation using your own analytics tool.
Using the QSERIES formula
Note that the structure of the QSERIES formula is as follows, with frequency as the third argument:
=QSERIES(quandlCode, dateRange, frequency, sortOrder, transformation, limit, headers, dates, transpose)
To return monthly data, put "monthly" in the frequency argument, like this:
The above formula will return monthly data for the XNAS/ACIW time-series.
The frequency argument can be "daily","weekly", "monthly", "quarterly" or "annual".
Note that the conversion process is very simple:
Nasdaq Data Link simply takes the last observation in the day/month/week/quarter/year and uses that as the daily/monthly/weekly/quarterly/annual datum.
This simple conversion process does not work well for time-series that contain percentage changes, period averages/totals (e.g. trading volume) or period extremes (e.g. high/low or OHLC for security prices). For such time-series, we recommend downloading the raw data and carrying out the required daily to monthly transformation using your own analytics tool.
Note also that you can only convert a time-series to a less granular frequency (e.g. daily to monthly) and never the other way around to a more granular frequency (e.g. annual to daily).
Using the Nasdaq Data Link Formula Builder
To return monthly data 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, like this:
- Click Next and follow the prompts. When you reach the Filters stage and can filter by Frequency, select Monthly from the drop down, like this:
- Enter any other desired filters and click Next. Follow the prompts to download the data.