Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I've got an issue with some date's, I'm trying to track some daily share index prices (data format shown below)
It is missing a year value's and I need to make this into a date format of DD/MM/YY, this goes as far back as 2013.
Day | Date | Open | High | Low | Close | Volume |
---|---|---|---|---|---|---|
Tuesday | June 03 | 9,949.96 | 9,954.78 | 9,917.66 | 9,924.52 | 25,249,588 |
Friday | May 30 | 9,926.73 | 9,970.77 | 9,924.63 | 9,943.27 | 103,680,132 |
Thursday | May 29 | 9,937.66 | 9,956.24 | 9,917.97 | 9,938.9 | 65,743,401 |
Wednesday | May 28 | 9,950.74 | 9,957.87 | 9,898.26 | 9,939.17 | 72,748,507 |
I'm also looking to get a percentage change of the closing price, but thought I'd need to get the date right first.
I'm relatively new to scripting, but I can't seem to crack this.
Thanks,
..this is more simple, readable and much better to understand:
Date(Date#(Date & ' 2013', 'MMMM DD YYYY'))
Probably easiest to clean it up in excel first. Or first load the data from 2013 and the data from 2014 in a second load. That way you can create the date field more easily with something like date#( Date & ' 2014', 'MMMM DD YYYY') as MyDate.
Hi Patrick,
you can try something like this:
Data:
LOAD Day, Date(Date#(Date & ' 2013', 'MMMM DD YYYY')) as Date, Open, High, Low, Close, Volume;
LOAD * INLINE [
Day, Date, Open, High, Low, Close, Volume
Tuesday, June 03, "9,949.96", "9,954.78", "9,917.66", "9,924.52", "25,249,588"
Friday, May 30, "9,926.73", "9,970.77", "9,924.63", "9,943.27", "103,680,132"
Thursday, May 29, "9,937.66", "9,956.24", "9,917.97", "9,938.9", "65,743,401"
Wednesday, May 28, "9,950.74", "9,957.87", "9,898.26", "9,939.17", "72,748,507"
];
Result:
NOCONCATENATE LOAD *, 1 - (Close/Previous(Close)) as ChangePct
Resident Data
Order By Date;
Drop Table Data;
- Ralf
Hi,
try below.
date(MakeDate(2013,num(month(date#(Date,'MMM DD'))),right(Date,2)),'DD/MM/YYYY')
Regards
ASHFAQ
Hi Ashfaq,
This worked, thanks alot
..this is more simple, readable and much better to understand:
Date(Date#(Date & ' 2013', 'MMMM DD YYYY'))
Hi Ralf,
You are correct, I was originally having some issues puting your method into my script, but it now works perfectly.
Thanks,