How to convert google timestamp

Dear all,

I have downloaded the google intraday backfill using the following thread

http://www.google.com/finance/getpr...d&f=d,c,o,h,l&df=cpct&auto=1&ts=1266701290218

And saved as a CSV but not sure how to convert the timestamp to date and time. Can anyone help me ? Sample data here:


EXCHANGE%3DNSE
MARKET_OPEN_MINUTE=540
MARKET_CLOSE_MINUTE=930
INTERVAL=300
COLUMNS=DATE CLOSE HIGH LOW OPEN VOLUME
DATA=
TIMEZONE_OFFSET=330
a1306294800 185.95 185.95 185.95 185.95 1567
2 186.7 186.9 185 185.25 339531
3 188 188.85 186.5 186.6 392544
4 188.1 188.25 187.5 187.95 136240
5 188 188.25 187.9 188.15 90887
6 187.25 188.05 187.05 188 77280

have you saw my thread ?
http://www.traderji.com/data-feeds/48683-free-charting-software-plus-free-data-feed.html

it will help you lot to convert decode google data.

Hi Ramadas,

Thanks for your thread. Yes, I have been using MT4 tweek.

But this decode is required for my own analysis and backfill the AMI thru excel.

Could you please help me in understanding the Google intra day data in terms of Timezone offset..

Thanks,
Siva

string market_open_min,market_close_min,interval,timezone_offset;
datetime dt_TS,dt_TSSync;
string s_TSRef;

CSVHandle = FileOpen(SymbolName + ".csv", FILE_CSV|FILE_READ,';' );
if(CSVHandle < 0) {Alert( SymbolName + "CSV File not present "+ CSVHandle); return(0);}

strdata = FileReadString(CSVHandle); // EXCHANGE%3DNSE
market_open_min = FileReadString(CSVHandle); // MARKET_OPEN_MINUTE=540
market_close_min = FileReadString(CSVHandle); // MARKET_CLOSE_MINUTE=930
interval = FileReadString(CSVHandle); // INTERVAL=300
strdata = FileReadString(CSVHandle); // COLUMNS=DATE,CLOSE,HIGH,LOW,OPEN,VOLUME
strdata = FileReadString(CSVHandle); // DATA=
timezone_offset = FileReadString(CSVHandle); // TIMEZONE_OFFSET=330

market_open_min = StringSubstr(market_open_min,19,0);
market_close_min = StringSubstr(market_close_min,20,0);
interval = StringSubstr(interval,9,0);
timezone_offset = StringSubstr(timezone_offset,16,0);

strdata = FileReadString(CSVHandle);

s_TSRef = ExtractStr(strdata); // Time stamp

if ( StringSubstr(s_TSRef,0,1) == "a")
{
s_TSRef = StringSubstr(s_TSRef,1,0);
dt_TS = StrToInteger(s_TSRef)+ gmtoffset ;
dt_TSSync = dt_TS;
}
else
{
d_TSCurr = StrToInteger(s_TSRef);
dt_TS = dt_TSSync + (StrToInteger(interval) * d_TSCurr);
}

i don't know how to do it in AMI , but you can get some clue from code i posted in previous post.

Ramadass,

Thanks for your code but could you please explain me the logic of converting in layman language. pls.

Siva

Can someone help me in converting this timestamp to date and time and EXCEL ??

Pls

Google time is in Unix format, To convert it in readable format using excel use following formula

=A1/86400 + 25569 + (5.5/24)
where,
A1 is the cell where you can copy your google datetime
Remember to pass only '1306294800' and NOT the preceding 'a'. Also to make it readable please change the cell format to 'Date'

Hope it helps

Hi Ash,

Fantastic. it worked.
Siva

What I was searching for, from a long time and looking forum to forums is found here thanks