Time series data

[rowbro]

Good day all. I’ve got a time series database for my economic data, and it generally works pretty well except I have one issue around data frequency in my queries.

In short, different indicators have different release frequencies, such as daily, monthly, quarterly, annual etc.

I have two specific issues I’d like to address: 1. When extracting data, if you enter the wrong data frequency in the form/query, it finds no data, and thus, I’d like to tweak the form/query to only show a list of available frequencies for the data once the indicator has been selected, not all the frequencies of all the data (from a frequency table). How would I go about this?

2. Where I’m trying to extract data on a less frequent basis than it is released (I.e. I want annual data and the data I have is monthly), I would like the query to either average, select the end of period, or sum (the correct one of these options will depend on the data, and the correct option will be noted in the database) the more frequent data to give the less frequent values. For example, if I have monthly diamond production data and I want to extract annual data, I would like the query to sum all monthly data for the year and show that value. How could this be done?

Thanks in advance!

 

[rowbro]

Summing data using dates and queries

Good day,

I am working on a time-series database and I am trying to make the data extraction as simple as possible. In my retrieve form, users can extract data by selecting time ranges for data, indicators, data frequency etc.

I am, however, trying to find a way to sum data if the user wants to query/extract data on a less frequent basis than it is released (for example the user wants annual diamond production data and I only have it by month). Is there some way to do this using sums in the query?

A sample of the database can be downloaded here: https://goo.gl/CbBJrp

 

[Joe4]

Re: Summing data using dates and queries

I have merged your two threads together, since they appear to be dealing with the same issue.
In the future, please do not post two threads on the same question, as per rule 12 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Micron]

Re: Summing data using dates and queries

1 - use a combo to present the frequency choices based on the indicator (whatever that is). Create a select query that does this, and use it as the combo's row source.
2 - probably you'd have to sum all the data and divide by the count of the frequency portion (week, month). Can't see how you can get monthly data for values that are only input once per year (annual data) which is how I interpret your statements. But you can get the count of days/weeks/months between 2 dates using DateDiff function if that helps you. Sounds to me like a lot of coding is required because of all the variables.