Retrieving and formating data from WSJ Online (2 part question)

[JGarces]

Dear Friends,

I have a two part request. I'm not an expert, so this has been very difficult to figure out. Any help is greatly appreciated

Part 1.
I produce a report that has the largest gainners and decliners from the WSJ. I'm using the following code to
retrieve that information for a particular day

Please Login or Register  to view this content.

Where B1 = historical date in format "YYYYMMDD" and the cell is named "NUMBER".

This works fine but I was wondering if there's a better way to retrieve the same data with different codes.


Part 2.
This is where I need help the most. The information has the same format as found in the WSJ online: Security name (ticker).

I would like to add a code that moves or places the ticker first then the name. The problem I found is that not all names and tickers have the same # of characters. The only constant is that the ticker (in parenthesis) is always at the end.

Here is a sample of what the code above retrieves.

Issue(Roll over for charts and headlines) Price Chg % Chg Volume
1 Kingsway Financial Services (KFS) $3.23 $0.47 17.03 34,209
2 Quad/Graphics (QUAD) 19.11 2.4 14.36 646,286
3 China Green Agriculture (CGA) 3.51 0.3 9.28 204,350
4 Vipshop Holdings ADS (VIPS) 14.56 1.17 8.74 309,799
5 RadioShack (RSH) 2.4 0.19 8.6 4,206,617
6 YPF ADS (YPF) 14.04 1.08 8.33 3,996,995
7 AVG Technologies (AVG) 15.24 1.16 8.24 2,171,207
8 Viad (VVI) 26.53 2 8.15 224,865
9 Luxfer Holdings ADS (LXFR) 11.94 0.84 7.57 21,696
10 Petrobras Argentina ADS (PZE) 5.19 0.36 7.45 120,268
11 E-House (China) Holdings ADS (EJ) 4.28 0.28 7 490,262


Thanks in advance,

Juan

[Chippy]

This works fine but I was wondering if there's a better way to retrieve the same data with different codes.

You're using a web query, which is the easiest way of retrieving the data, but not the fastest. Faster ways involve XMLhttp and HTMLdocument but these require more code to parse the web page according to its HTML structure.

I would like to add a code that moves or places the ticker first then the name.

Here is a sample of what the code above retrieves.

Issue(Roll over for charts and headlines) Price Chg % Chg Volume
1 Kingsway Financial Services (KFS) $3.23 $0.47 17.03 34,209

Try the following code. The sample Issue shown above would be changed to "(KFS) Kingsway Financial Services".

Please Login or Register  to view this content.

[JGarces]

Hi Chippy,

Thank you for the change suggestions, they worked nicely and faster.

Thanks again,

-- Juan