Add Datetime rows to a datatable in the DataTableBuilder for Google Scripts
I've built an intraday macroeconomic forecast system in R. It uses intraday market data to update an forecast. Basically, I have a forecast series that looks like financial market data.
I want to show these data using a Google Chart in the Google App Script platform. To keep the datatable small (and thus load time down), I only want to plot intraday values for the five most recent days. For older days, I want to save only the last forecast of the day. That is, I want to have a time series plot with two frequencies of data which the user can choose from.
Here's and example of how I write the data from R (columns separated with "|"):
OBSlatest.forecast|freq 2014-03-10 14:45:00 EDT|4.09662|D 2014-03-11 15:00:00 EDT|4.10075|D 2014-03-12 14:57:00 EDT|4.08862|D 2014-03-13 14:57:00 EDT|3.998|D 2014-03-14 15:00:00 EDT|3.9843|D 2014-03-10 09:30:00 EDT|4.13823|I 2014-03-10 09:33:00 EDT|4.13468|I 2014-03-10 09:36:00 EDT|4.14078|I
I'm currently translating this table into Google App Script with
var data = Charts.newDataTable() // Set data table columns .addColumn(Charts.ColumnType.DATE, "Time") .addColumn(Charts.ColumnType.NUMBER, "Forecast") .addColumn(Charts.ColumnType.STRING, "Frequency") // Fill in rows .addRow([new Date(2014,3,13,14,57, 0),3.998 ,'D']) .addRow([new Date(2014,3,10,9,30, 0),4.13823 ,'I']) // More rows, but they all look like this, either a 'D' or 'I' at end
I've got a rough script up now that uses a category filter so that the user can choose to show either the daily frequency 'D' or intraday frequency data. However, I seem to be using the Date() function wrong. My data are all out of order and the dates don't match what I've loaded. My understanding the of Date() function's arguments is: Date(year, month,day,hour,min,sec), yet I only seem to be able to get the year to show up right in my chart. For example, the first row I'm adding in my code above yields "13 apr 2014 11:57:00" when my expectation is that it'd give "13 mar 2014 13:30:00"!
Nearest I can tell is that it is subtracting three hours from my time and one month from my month. Any ideas what's going on? This approach works fine for an HTML implementation of Google Charts, which I have up at: EfficientForecast.com
Thanks for any help!
The month parameter is zero-based, so subtracting one is the correct/expected thing to do. Check the MDN docs.
About your hour issue, I can not reproduce it. So I imagine it is something specific to your system. Please check what your Google Drive timezone setting is, then this Apps Script's setting and lastly your OS timezone setting. I imagine this is happening because some of these configurations are not matching.