Excel Web Query, how to separate data

Discussion in 'Mac Apps and Mac App Store' started by whbunn, Dec 20, 2016.

  1. whbunn macrumors member

    whbunn

    Joined:
    May 29, 2009
    #1
    The script below is used in Excel to get stock prices from Yahoo Finance.

    The query gets the stock name and last price and puts in the cell designated when you set up the query.

    The name and price are put in the same Excel cell. I would like to have the data put in cells next to each other horizontally. Eg cell A3=Apple, and cell B3 = $110.89. (see below)

    How can I modify query to put the multiple facts about a security in separate cells horizontally?

    WEB

    1

    http://finance.yahoo.com/d/quotes?s=AAPL&f=nl1



    Screen Shot 2016-12-20 at 3.42.19 PM.png
     
  2. campyguy macrumors 68040

    Joined:
    Mar 21, 2014
    Location:
    Portland / Seattle
    #2
    AFAIK you want a line feed, or carriage return - in 2011 I used "/r" (without the quotes, of course) in my scripts. I haven't tested this in Excel 2016 but I'd hazard a guess it's still the same. The Excel line feed character is achieved by pressing Option-Enter at the end of your line.

    In Windows I use "/r/n" (without the quotes) and Alt-Enter, respectively...
     
  3. whbunn thread starter macrumors member

    whbunn

    Joined:
    May 29, 2009
    #3
    --- Post Merged, Dec 21, 2016 ---
    Thanks for your input. Unfortunately I could not get the results I was looking for. All data ie. name and price put in same cell. I create the web query in word.

    How would you write this query to get last price and name? If "l1" is price, "n" is name. I need price and name displayed as in original post

    I am not a coder, keep it simple.


    WEB
    1
    http://finance.yahoo.com/d/quotes?s=AAPL&f=l1
     
  4. campyguy macrumors 68040

    Joined:
    Mar 21, 2014
    Location:
    Portland / Seattle
    #4
    No worries. I'm at a loss, sorry. The header you're using ceased in its use in 2011 AFAIK. I'm still using the Win version of 2016 as the Web Query is built in. I'll ask my biz partner when I see him in a couple of days, unless I come up with something before then. My line break proposal did work on my existing scripts, a reason behind my suggestion or I would have kept my lips zipped...
     
  5. mk313 macrumors 6502

    Joined:
    Feb 6, 2012
    #5
  6. whbunn thread starter macrumors member

    whbunn

    Joined:
    May 29, 2009
    #6
    Thanks for your input. For various reasons I am not moving to excel 2016 yet. I am aware of its feature.

    again, thanks
    --- Post Merged, Dec 22, 2016 ---
    Thanks for taking the time to reply.

    Bill
     

Share This Page