Getting data with web query and rearrange the data

  • I have this worksheet that will get some chinese stock live data from a webpage using web query
    the website is http://quote.cfi.cn/quote_600515.html


    When i input stock ticker name in sheet2 A1:A100, the vba will get the data and arrange as in sheet2.


    Sheet1 I:M is the data that i manually arranged in Volume open high low close, with the chinese characters that needs to be removed. How can this be done using VBA, thanks in advance. (web query range is in sheet1 B:F)


    [ATTACH=CONFIG]68599[/ATTACH]


    forum.ozgrid.com/index.php?attachment/68600/

  • Re: Getting data with web query and rearrange the data


    From what I understand you need vba to extract just the numbers between the Chinese Characters ?


    From [TABLE="width: 122"]

    [tr]


    [TD="class: xl64, width: 122"]成交量:45953手[/TD]

    [/tr]


    [/TABLE]
    to 45953 ?

  • Re: Getting data with web query and rearrange the data


    yes, i just want the numbers, any other characters should be removed, and is there a straight forward way to get the data and list to cells as in Sheet2 using VBA without all the other removing of characters steps

  • Re: Getting data with web query and rearrange the data


    the way i want to do this, is that i will input stock tickers symbol eg., 600888, 600515, etc, to Sheet2 A2:A100, the vba will use the web query to get the data of this stock and list the Volume Open High Low Close data to B2:B100, as in sheet2, at the moment, there is too many chinese characters messing up the table

  • Re: Getting data with web query and rearrange the data


    Here is the answer to your inquiry. The IsNumber Function can be credited to Rick Rothstein:



    Thread is also at: THIS LINK

    Matt Mickle
    Using Excel 2010,2013 & 2016

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!