Excel: formatting dates from Yahoo....

[Bluewave]

Hi, I'm hoping some clever Excel bod can work this out!

I am using Excel's web query to import Yahoo financial data to be updated on a daily basis. When I use this link: http://uk.finance.yahoo.com/d/quotes.csv?s=^dji&f=d1&e=.txt

...the following is imported into an Excel cell: 04/20/2005 (ie. date at the time of import in mm/dd/yyyy format).

Now, how can I force this American date format into the preferred British format of dd/mm/yyyy? Simply using the date formatting tool does not work presumably because the cell is a web query.

Any ideas?

 

[FetteredChinos]

copy the entire data, and then right click.

paste special.

tick the Values box.

that should remove any webby stuff.

then you should be able to do some date formatting.


if you still havent had any luck, please attach the file on here and i'll have a go for you.

FC

 

[A Dashing Blade]

Many ways to do it, here's some code (used because all Bloombergs dates are American). . .


Function UStoUK_Date(i_ACell As Range) As Date

Dim CellContents As Variant
Dim Month As String
Dim Day As String
Dim Year As String
Dim Counter As Integer
Dim Counter2 As Integer

UStoUK_Date = 0
CellContents = i_ACell.Value

Counter = InStr(CStr(CellContents), "/")
If Counter > 0 Then
Month = Left(CellContents, Counter - 1)
Counter2 = InStr(Counter + 1, CStr(CellContents), "/")
Day = Mid(CellContents, Counter + 1, Counter2 - Counter - 1)
Year = Right(CellContents, Len(CellContents) - Counter2)
Else
UStoUK_Date = 0
GoTo TidyUp
End If

UStoUK_Date = DateSerial(Year, Month, Day)

TidyUp:

CellContents = Null
Month = ""
Day = ""
Year = ""
Counter = 0
Counter2 = 0

End Function


To do it in a formula you could use the Left(), Right() and Mid() functions wrappped up in a DateValue() fuction

Shout if you need more help

 

[Bluewave]

F.C. - thanks, but I don't really want to remove the webby link as the data needs to be updated daily. The idea was to update the date from Yahoo at the same time as the financial data, thus ensuring the reader of the data that it was from the present day.

But mentioning Paste Special does bring up another point: why can't this special paste tool copy web linked cells? The copy loses the link. Doh!

A Dashing Blade: wow, thanks for that! And gulp... I've gotta learn some V.B. then?! Umm, think I might need some help applying this code to the cell... but I'll have a go and get back to you.

 

[FetteredChinos]

cant you just adjust your eyes to read the dates the other way around?

fc

 

[Bluewave]

cant you just adjust your eyes to read the dates the other way around?

fc

Yeah, swapping my eyes over would be easier.

 

[FetteredChinos]

i tried that a while ago.

i havent looked back since.

 

[Bluewave]

I think I need a brain swap! Ok, how do I apply Dashing Blades code to a cell? I've tried starting a new Macro Module in V.B. and copied the code there. But when I return to Excel and run the Macro it reports: "Ambiguous name detected: UStoUK_date

 

[JonnyT]

Try removing the Option Explicit at the top of the module as I do not beleive there was a DIM statement for the variable.

JonnyT

 

[Bluewave]

Ok, I think I am getting somewhere now. Dashing's code is a User Defined Function, which is set up through the V.B. Editor, and applied by clicking on the fx symbol in Excel, selecting User Defined and then the function name.

However the function doesn't seem to do what it's supposed to. The result is never a swap of the day and month, but often comes out as 00-Jan-00 or even the serial code for the date!

Edit: Sometimes it works, sometimes it doesn't! It seems to help if the result cell is already formatted to the British date format.

 

[A Dashing Blade]

. . .
However the function doesn't seem to do what it's supposed to. The result is never a swap of the day and month, but often comes out as 00-Jan-00 or even the serial code for the date!

Edit: Sometimes it works, sometimes it doesn't! It seems to help if the result cell is already formatted to the British date format.

"even the serial code for the date" - er . . . that's how dates are represented in Excel, as you say, format to your desired date format (essentially there are 3 independant features/elements of a cell in excel, the formula, the value and the format).

often comes out as 00-Jan-00 . . . . hmmmm, could be that you have trailing or leading blank spaces in the cell's value, inwhich case substitute

CellContents = i_ACell.Value

to

CellContents = Trim(cstr(i_ACell.Value))

what this does is remove leading/trailing space characters from the (converted to text) value shown in the cell


edited to add
JohnnyT - ahem . . . ALL my variable are dimmed & options explicit should be at the top of all code modules, I throw a total wobbly at any trader/quant writing their own code who doesn't do this

 

[Bluewave]

Many thanks for your help Dashing Blade!

Everything is working well. I just press Refresh and all the right data is pulled in and worked on automatically. Sorted!

 

[A Dashing Blade]

Many thanks for your help Dashing Blade!

Everything is working well. I just press Refresh and all the right data is pulled in and worked on automatically. Sorted!

My pleasure (& my profession!)