Excell

R

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. Thank you for any and all help.

Tim
 
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. I would appreciate any help you could give me. Thank you.

Tim


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
 
Quote from raven4ns:

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. I would appreciate any help you could give me. Thank you.

Tim


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
More...

Unless you have a common delimiter, such as a comma or a tab, its going to be difficult to get the values in the columns correctly (the spaces throw it off). You may need to do some data conditioning before pasting or importing into excel.
 
Quote from 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. Thank you for any and all help.

Tim
More...

There are a few ways to do this (some more simple than others). This is a fast and simple approach. You lose a bit of the middle strings (like corp.), but I figured most of what you need is here.
Also, you can get the strings back with a little ingenuity, if you absolutely need them.

2ik7brs.jpg
 
Hello Dtrader,
You certainly seem adept with Excell. What I did was separate the numbers in notepad from the text and managed to get the info arranged the way I needed. Thank you for your kindness in helping as well as the other people also.
How do I add (-TC ) to the end of each symbol as these are CDN stocks and I use Esignal as my data provider? Once again thank you for your help, it is very much appreciated.

Kindest regards,

Tim
 
no problem TC. Thanks for the words of appreciation. It's good to hear feedback once in a while.:)

Anytime you want to add something to a symbol from a column, simply go to another column and enter an equation as follows:

=A1&"-TC"

Where the 1st cell from the column you want to apply is A1 in this case (or B12 or whatever cell you start at), then drag it to the end of the list you want to apply it to.

If you want a blank in-between the symbol and the -TC string that you want concatenated, type

=A1&" "&"-TC"

Good Luck. Let me know if it doesn't work for any reason.
 
You must log in or register to reply here.
Back
Top