Help Needed

[raven4ns]

Hello

I have found the information I want but I can't sort it out with excell. I have the quarterly volume stats for stocks on the TSX but when I import them to Excell they are crammed together in 1 column instead of being in 3 columns. How do I sort them so I can use the data? The data includes the symbol, the company name and the volume but it all ends up in the "A" column instead of A,B and C.
This is what the data looks like now. What I need is a way to sort it into separate columns, one for ticker-symbol, one for name and 1 for volume. I tried the text to columns but couldn't get it to sort properly.

TICKER_SYMBOL NAME VOLUME
AAH Aastra Technologies 2,038,118
ABG Arawak Energy Corp 7,118,039
ABH AbitibiBowater Inc. 6,127,553
ABO.B Arbor Memorial Cl B 92,219
ABT Absolute Software J 9,185,693
ABX Barrick Gold Corp 191,652,208
ABZ Aber Diamond Corp 3,697,961
AC.A Air Canada Cl A VV 20,831,220
AC.B Air Canada Cl B 4,577,285
ACC Amica Mature Lfstyle 471,629
ACE.A ACE Aviation Hldg VV 42,816,493
ACE.B ACE Aviation Hldg V 15,967,397
ACE.NT.A ACE Aviation 4.25%Nt 21,708,000
ACF.UN IAT Air Cargo Tr Un 365,412
ACM.A Astral Media Cl A NV 7,824,964

I would appreciate any help you could give me. Thank you.

Tim

 

[0007]

Hello

I have found the information I want but I can't sort it out with excell. I have the quarterly volume stats for stocks on the TSX but when I import them to Excell they are crammed together in 1 column instead of being in 3 columns. How do I sort them so I can use the data? The data includes the symbol, the company name and the volume but it all ends up in the "A" column instead of A,B and C.
This is what the data looks like now. What I need is a way to sort it into separate columns, one for ticker-symbol, one for name and 1 for volume. I tried the text to columns but couldn't get it to sort properly.

TICKER_SYMBOL NAME VOLUME
AAH Aastra Technologies 2,038,118
ABG Arawak Energy Corp 7,118,039
ABH AbitibiBowater Inc. 6,127,553
ABO.B Arbor Memorial Cl B 92,219
ABT Absolute Software J 9,185,693
ABX Barrick Gold Corp 191,652,208
ABZ Aber Diamond Corp 3,697,961
AC.A Air Canada Cl A VV 20,831,220
AC.B Air Canada Cl B 4,577,285
ACC Amica Mature Lfstyle 471,629
ACE.A ACE Aviation Hldg VV 42,816,493
ACE.B ACE Aviation Hldg V 15,967,397
ACE.NT.A ACE Aviation 4.25%Nt 21,708,000
ACF.UN IAT Air Cargo Tr Un 365,412
ACM.A Astral Media Cl A NV 7,824,964

I would appreciate any help you could give me. Thank you.

Tim

Your problem is that the text isn't delimited properly (ie Excel needs to know the start & finish of each column).

Use the data/import external data menu and when you get to the bit where it asks you about delimiters, you choose accordingly. With the data above the only delimiters you have are spaces which doesn't work completely because the spaces don't always indicate cols where you want them.

If you can re-dnld the data as a CSV file (delimiters = commas) it'll work. If not, you have to find a method of inserting some form of delimiter at the appropriate place.

There are text manipulation progs that can do this by use of custom rules but the easiest way is probably to manipulate it in a word processor (saving as a text file to remove wp codes) using the search and replace routine eg for a small amount of data add an asterisk symbol to each space and then use asterisk=space as your delimiter having first removed any asterisks which wrongly indicate col start /end. You can also do it by converting the text to table and then merging / manipulating cols (ok, it's easy if you're used to it)

Sounds tedious but not really. Easiest solution is to dnld delimited data - most is these days so check your source. Hope this helps.

 

[grantx]

Raven,

I may have a very simple solution. First:

How many stocks are there?
How frequently will the prices be updated?

Grant.

 

[raven4ns]

Thank you everyone for your help, I appreciate it very much. I managed to get it done although the process was a little convoluted. Thank you again.

Kindest regards,

Tim