Excel Web Query, how to separate data

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

  1. whbunn
    Expand Collapse
    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
    Expand Collapse
    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
    Expand Collapse
    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
    Expand Collapse
    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
    Expand Collapse
    macrumors 6502a

    Joined:
    Feb 6, 2012
    #5
  6. whbunn
    Expand Collapse
    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