+ Reply to Thread
Results 1 to 4 of 4

Web query to download web page data to excel in text format

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    On the move
    MS-Off Ver
    Excel 2010
    Posts
    8

    Web query to download web page data to excel in text format

    Hi friends,

    I have been trying to download stock data from google finance link (http://www.google.com/finance/getpri...&f=d,o,h,l,c,v) into excel and have created vba macro for the same.

    The below is the sample data I get from the above link.

    1,3882,3890.8,3862.35,3890.8,724
    2,3873.6,3879.9,3873.6,3879.9,161
    3,3880,3880,3873.6,3873.6,403
    4,3865.3,3878.75,3865.3,3877.15,281
    ...
    ...
    372,3898.65,3900,3898.55,3900,259
    373,3897.2,3900,3896.05,3898.65,638
    374,3890,3896,3890,3895,474
    385,3901.4,3901.4,3901.4,3901.4,34

    Kindly note the data highlighted in bold in the above sample. I can download all into excel as a text format (except for the bold data) since their is a comma AND decimal points both in all other data samples. However in the data highlighted in bold above, there is no decimal point only comma is appearing. When this data is transferred to excel it is converted into numeric. And this is what I do not want. I want the same data in text format with commas as I further have to do text to columns and use comma as delimiter. The data what is transferred for the highliget data point is 3.7438903896389E+21.

    Experts kindly advise me on what code I should put in the web query to solve this problem.

    Thanks,

    R. Pandit
    Last edited by panditji; 09-14-2012 at 10:54 PM.

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Web query to download web page data to excel in text format

    Hello and welcome to the forum,

    You will have to tell Excel to format the cell first as text before the import. You can do this cell by cell:

    Please Login or Register  to view this content.
    with r being the row number

    or the whole column:

    Please Login or Register  to view this content.
    or the whole worksheet

    Please Login or Register  to view this content.
    abousetta
    Last edited by abousetta; 09-15-2012 at 12:30 AM.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    09-11-2012
    Location
    On the move
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Web query to download web page data to excel in text format

    Thanks abousetta. I could get what I wanted. Thanks for your help once again.

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Web query to download web page data to excel in text format

    Glad I could help. If you are satisfied with the responses then please mark the thread as solved.

    Good luck.

    abousetta

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1