Excel - get external data, delimiter prob?

900xs

Junior member
42 0
I can import data from this site :

http://sc.sc8.finance.lycos.com:443/?User=demo&Pswd=demo&DataType=DATA&Symbol=$indu&Interval=1

the data appears but not in the correct format i.e. columns are not dividing in the right place so it may be something to do with the delimiters (comma on the web page!) seems that the data is imported as a table.

I just want the correct data in the correctly spaced column :)

I can import it as a text file (correct spacing) but then cannot refresh it (automatically - every minute)

I have Excel 2000

Any ideas out there

Thank you
 

sidinuk

Established member
624 5
when you try to open the file, Excel will run the import wizard. Make sure 'delimited' is selected on the first page and 'comma' is ticked on the second page. On the 3rd page you can convert the date by highlighting column A and selecting the correct date format from the list.

When the file has opened save it as an Excel file.

HTH
 

mutantcar

Active member
123 0
900xs,

Correct me if I'm wrong, but you're using a Web query to bring the data in?

After a quick look, I think you'll need some code (macro) to format the dates etc. correctly. If you don't know how to do this, I'll have a further look tomorrow.

Mute.
 

900xs

Junior member
42 0
Thank you, Thank you: sidinuk, edeast, mutantcar.

That did it just as I had hoped, great advice...............ah! but then I had it set to refresh at 1 min interval and......in cell A1 the data arrived again (in the original comma delimited syle)

don't have the experience needed......but will return tomorrow to do battle again :)

this is frustrating......but will rest easier with all your help.

Thanks

900xs
 

900xs

Junior member
42 0
yes mutancar......using a Web query. It worked just fine until the first refresh (after 1 min)......however I'm confident it can be solved. I have tried for a long while to get data automatically into an excel file. (doesn't seem to exist ........not to my (tired) web browsing eyes anyway.)
 

mutantcar

Active member
123 0
I've done this some time ago with yahoo data, but from what I can remember had lots of problems with it - something to do with my connection going down. I may have been on dial-up then though.

Formatting's not difficult to solve, (Have you tried 'Preserve formatting' in query properties?) but is for another day - or later today anyway!

Cheers,

Mute.
 

900xs

Junior member
42 0
Here is what I mean.

if you download this, it will change after 1 min because: auto refresh is set at 1 min and the columns I want refreshed, are not.

any thoughts??
 

Attachments

  • lycos.xls
    49 KB · Views: 498

rog1111

Established member
673 10
900xs

Since your data has 7 fields, you could just put formulae in columns B:H eg
In cell B2 put =left(a2,6)
In cell c2 put = mid(a2,8,4)
etc

If the field lengths vary then you could make use of Excel's FIND worksheet function & look for the commas in the A column. Also TRIM removes spaces & might be useful.

Then each time the data refreshes you'll have what you need in columns B:H

rog1111
 

900xs

Junior member
42 0
Thanks rog1111.........that worked a treat (learnt something new:)
 
 
AdBlock Detected

We get it, advertisements are annoying!

But it's thanks to our sponsors that access to Trade2Win remains free for all. By viewing our ads you help us pay our bills, so please support the site and disable your AdBlocker.

I've Disabled AdBlock