Excel here

Friday, September 15, 2006

Using Web Queries to Import Data to Excel

The other day, I bumped upon this powerful and really simple tool in excel. I must say, its one of those gems that is kept inside excel. I felt like a kid getting excited discovering a candy in his pant pockets. Well, to the point now!

Often, business data and analytics deal with lots of Ctrl+C, Ctrl+Vs. For example you might be downloading a financial statement from Google Finance or a rating for a select list of companies form Fitch! or AM Best or even simple top 250 movies list from IMDB. What we generally do is open the web page and copy/paste the data on to our excel sheets. In the end when we perform the analysis and conclude something, it is always "as of some past date". Well, with the help of Web Queries, the analytics can be real time, well, almost.

5 Steps to using Web Queries to Import Data to Excel Sheets:

  1. First up, locate [Just locate, you need not load that page in excel] the site / webpage you want the data to be copied from. Remember, this should be on public domain without login access. Simply put, if you type the URL in the browser, the page should load, no matter whether you are logged in to that site or not. (Ex: IMDB Top 250 Movies)
  2. Open Excel. Go to Data -> Import External Data -> New Web Query

  3. In the Dailog box for webquery enter the URL you want and say 'Go'.

    Select the table you want like this. The table outline would turn 'Green' or something differant. Click on it and you would see the mark becoming like a tick like this.
  4. Say Import. In the next dailog enter the starting cell. You can click on the properties to change the behaviour of webimport, but it may not be necessary. While the import is going on you see a cyan colored globe trotting around itself on the status bar. If you cant, blame your office vendor.
  5. Once the table is loaded, you can right click anywhere on it and say Refresh Data! and it would fetch the new data from web automatically.

Potential Applications of Web Queries Data Import Tool:

  • Automated Portfolio Managers: Enable automatic refresh on File open and load MF, Share price related data from public sources.
  • Realtime graphs, Charts and dash boards: You might have some application running on your LAN and generating data. Now assume there is a way to convert such data into HTML on the fly. Bingo! you dont have to copy paste the data to excel now. Just link up once and you are done.
  • So many more, I think of almost anything and I see a possibility of using Web Query Imports to simplify that task.

69 Comments:

  • What to do when the web page has Login Page?

    By Blogger Ilan Leizgold, at 11/21/2006 03:37:00 AM  

  • as I said, this technique is for the pages available on public domain. If the page can be accessible from cookie based login then i think it might work. But if doesnt, then I think you have to use different technique altogether...

    By Blogger Chandoo, at 11/28/2006 10:00:00 PM  

  • Good and Imp. Information.

    By Blogger Satya Prakash Karan, at 7/11/2007 09:43:00 AM  

  • Hi,

    Once again after crash Nifty has started going up. Now we suggest all rises should be used as an opportunity to exit old long positions.
    This bull run will continue for few more days. Overall market is in bearish mood as in medium term its just a small rally due to short covering
    and result season.


    Happy Trading,

    ShareGyan

    By Blogger share, at 9/27/2008 12:06:00 AM  

  • Thank you very much for this tip - it has saved me a lot of time!!!

    By Blogger Emir, at 2/16/2009 06:59:00 AM  

  • This comment has been removed by the author.

    By Blogger Emir, at 2/16/2009 07:00:00 AM  

  • This blog is nice and informative,its our pleasure to post a comment on this blog created by the webmaster


    Now as such we had seen in the month of Feb'09 that volitality was very much there considering the various factors deciding the movement of the Indian Stock Market

    Now in the coming Month of March'09 which is also the year's closing period,also the important Policies would might be declared around the world will be deciding the movement of Indian Stock Market


    Happy Trading a Head


    Queries are welcomed

    09871142419
    09212663485


    Knowyourprofit@gmail.com


    KnowYourProfit

    By Blogger KnowYourProfit, at 2/26/2009 02:30:00 AM  

  • This blog is quite nice and informative, it is our pleasure to post a comment on this usefull blog created by a webmaster

    Now as such we all know that in India Elections are just around the corner which would inturn effect the movement of the
    Indian Stock Market which means the time has come when a common man should start thinking of investing in the Indian Share Market
    which would help him to increase the invested amount because one should not avoid the Opportunities come in Daily Life

    We welcome your Quieries at


    KnowYourProfit

    By Blogger KnowYourProfit, at 6/04/2009 12:31:00 AM  

  • I have an issue wherein the Refresh does not work on file open. However if i do the refersh after open, it works fine.. Any clue what's the problem

    By Blogger hram, at 6/25/2009 06:27:00 AM  

  • We are sell wow gold and wow power leveling wow gold

    By Blogger reyt, at 7/13/2009 12:35:00 AM  

  • works in StarCalc and OpenOfficeCalc also...

    Insert
    Link to external data
    (insert url)

    then wait while it loads in the URL

    voila, there it is

    I use it for NHL score updates to my fantasy betting book 'sheet'

    By Blogger Sniper Boss, at 10/29/2009 08:03:00 AM  

  • One of the best tutorials I have seen on Web Queries. Showed me exactly what I was looking for! Thanks a lot!

    By Blogger Shripad, at 11/05/2009 03:14:00 AM  

  • Dear Visitor,
    Market is showing some good move. Nifty traders are now confused if they should go short in Nifty from current level of 5400 or should go further long. NSE and BSE are the two major stock exchanges of Indian stock market.

    Keeping in mind that many investors and traders are very much confused with the current market move, we have stared posting Free technical research reports
    . These reports are highly accurate and are available for free.


    Regards
    BUZZINGSTREET

    Stock market investment if done with proper research and updated knowledge than it can give very lucrative results. There are four basic golden rules of stock market which are to be followed
    (thebuzzingstreet1@gmail.com)http://www.buzzingstreet.com

    We will be discussing the various benefits of speed trading and its various coefficients using which one can make the most out of their investments in the Indian stock market including both Nifty and Sensex (NSE and BSE)

    By Blogger buzzingstreet, at 7/28/2010 01:15:00 AM  

  • Dear Visitor,
    Market is showing some good move. Nifty traders are now confused if they should go short in Nifty from current level of 5400 or should go further long. NSE and BSE are the two major stock exchanges of Indian stock market. Keeping in mind that many investors and traders are very much confused with the current market move, we have stared posting " title="technical research reports"> technical research reports These reports are highly accurate and are available for free.

    Regards
    Stock market investment if done with proper research and updated knowledge than it can give very lucrative results. There are four basic golden rules of stock market which are to be followed. (thebuzzingstreet1@gmail.com)www.buzzingstreet.com


    Nifty is on its peak now and is turning volatile. Investors are suggested to book long delivery positions soon. As after some more upmove we can see profit booking in the market soon. Investors are suggested to grab quality stocks at lower level again.

    By Blogger buzzingstreet, at 10/18/2010 12:37:00 AM  

  • The blog created by the webmasters is really a informative and nice we would also like to add share some news.Now as
    such we know the indian stock market now a days is trading in a consolidation stage where a active Trader can make
    most out of it even in these conditions where less movement is there.

    Some sayings are always remember

    Wide diversification is only required when investors do not understand what they are doing!!!
    Warren Buffett

    www.KnowYourProfit.com

    By Blogger KnowYourProfit, at 1/03/2011 10:38:00 PM  

  • Hi,
    Lot of global tensions is going on at this time. Japan is expected to pull out its money from the global market as they want to revamp their country now. In current scenario anything can happen in the Share market Investors are advised not to panic and stay invested only safe traders and investors should exit their long positions on every high and one can use every decline as an opportunity to enter market again.
    Regards
    SHARETIPSINFO TEAM

    By Blogger sharetipsinfo, at 4/25/2011 04:18:00 AM  

  • I really appreciate your post and you explain each and every point very well.Thanks for sharing this
    information.And I’ll love to read your next post too.
    Regards
    Stock Tips

    By Blogger Stock Tips, at 5/19/2011 09:53:00 PM  

  • Very good post, I was really searching for this topic, as I wanted this topic to understand completely and it is also very rare in internet, that is why it was very difficult to understand.
    Thank you for sharing this.
    regards:
    share tips

    By Blogger Share Tips, at 5/30/2011 02:57:00 AM  

  • very nice post sir.i just love it.thanks for sharing this with us.
    for two days free trail pack please contact on 0731-4295950

    By Blogger Radha, at 6/24/2011 03:10:00 AM  

  • very nice post sir.i just love it.thanks for sharing this with us.
    for two days free trail pack please contact on 0731-4295950

    By Blogger Radha, at 6/24/2011 03:10:00 AM  

  • Nice blog would like to add that NSE and BSE are one of the most superior stock exchanges of India. If you wish to earn good money from the share market then you need to understand the functionality of the stock market properly.

    Indian stock market offers lot of earning opportunities still many less traders earn from it. Now the question is who earns from the share trading? To be honest only those who rely on stock research as no one can earn big by speculating in the market.

    Regards
    SHARETIPSINFO TEAM

    By Blogger sharetipsinfo, at 7/27/2011 10:44:00 PM  

  • When market is bullish everyone want to join stock

    market and wish to start trading but in bearish

    market condition why every one is scare of joining

    share market?
    We believe bearish market is as

    good as bullish market. Traders can get many

    trading opportunities in both types of trends.
    Regards
    SHARETIPSINFO

    TEAM

    By Blogger sharetipsinfo, at 9/14/2011 09:14:00 AM  

  • In this article we shall be discussing about the various ways in which one can make as much profits as maybe possible and how we can select securities to make investments. Investing in securities can be profitable only if investment is done with proper research

    By Blogger sharetipsinfo, at 10/19/2011 10:33:00 PM  

  • In this article we shall be discussing about the various ways in which one can make as much profits as maybe possible and how we can select securities to make investments. Investing in securities can be profitable only if investment is done with proper research

    By Blogger sharetipsinfo, at 10/31/2011 12:08:00 AM  

  • This blog is very appropriate and useful and i really appreciate your time and effort which u have spent to write the valuable blog. I would like to thank you for sharing.

    Regards,
    GUGU
    stock tips

    By Blogger Paul, at 12/18/2011 10:51:00 PM  

  • If you are trading in NSE, BSE, MCX and in NCDEX then let sharegyan give you all stock trading gyan

    By Blogger sharegyan, at 12/21/2011 12:22:00 AM  

  • If you are trading in NSE, BSE, MCX and in NCDEX then let sharegyan give you all stock trading gyan

    By Blogger sharegyan, at 1/13/2012 12:47:00 AM  

  • Thanks for the post. Here’s a post which shows how to convert your excel to web in minutes http://blog.caspio.com/integration/convert-ms-excel-to-web/

    By Blogger Cameron Reid, at 1/19/2012 01:14:00 PM  

  • If you are trading in NSE, BSE, MCX and in NCDEX then let sharegyan give you all stock trading gyan

    By Blogger sharegyan, at 2/29/2012 12:47:00 AM  

  • Commodity trading is gaining lot of popularity in India. MCX and NCDEX are most popular commodity exchanges of India. MCX and NCDEX both offers hi tech platform for commodity trading. Commodity trading should have clear picture like how they can earn money from ncdex or mcx trading and for that they need to track various listed commodities and should rely on research rather than speculation.
    http://www.sharetipsinfo.com/effect-sharemarket.html

    By Blogger sharetipsinfo, at 3/07/2012 04:25:00 AM  

  • Way2profit provides research based stock market tips in Indian stock market covering NSE, BSE and MCX exchanges

    By Blogger way2profit, at 3/12/2012 05:39:00 AM  

  • Commodity trading is gaining lot of popularity in India. MCX and NCDEX are most popular commodity exchanges of India. MCX and NCDEX both offers hi tech platform for commodity trading. Commodity trading should have clear picture like how they can earn money from ncdex or mcx trading and for that they need to track various listed commodities and should rely on research rather than speculation.

    By Blogger sharetipsinfo, at 3/26/2012 10:48:00 PM  

  • Thanks so much for this! I haven't been this moved by a blog for a long time! You’ve got it, whatever that means in blogging. Anyway, You are definitely someone that has something to say that people need to hear. Keep up the good work. Keep on inspiring the people!

    mcx gold tips,crude tips,silver prices

    By Blogger vishal, at 3/31/2012 04:31:00 AM  

  • If you are trading in NSE, BSE, MCX and in NCDEX then let sharegyan give you all stock trading gyan

    By Blogger sharegyan, at 4/10/2012 11:55:00 PM  

  • thanks for this post. I especially think #1 is a great tip., This post makes a great point about focusing your efforts.

    stock tips,equity tips | Currency Trading Tips,Forex Tips,Forex Pairs

    By Blogger Paul, at 5/03/2012 01:06:00 AM  

  • It's glad to see good information being convey. Its a very nice written, and I really like this blog. Thank you for sharing this info.

    Regards: Share Market Tips

    By Blogger Free Intraday Stock Tips, at 6/29/2012 10:35:00 PM  

  • I've been recently wondering about the identical issue personally lately. Pleased to see a person on the same wavelength! Nice article.

    Stock Tips

    By Blogger commodity tips provider, at 9/04/2012 12:04:00 AM  

  • Hi, you explained the topic very well. The contents has provided meaningful information thanks for sharing info.

    Commodity Tips

    By Blogger AshaSoni, at 12/13/2012 04:16:00 AM  

  • Share Market Tips thanks for the info!!

    By Blogger profitmirror seo, at 1/22/2013 01:22:00 AM  

  • Your suggestions on excel is very nice. i was looking for this kind of blog.we have expertise in stock,commodity,mcx and ncdex trading.
    regards,
    mcx tips

    By Blogger sta-stock-commodity-tips, at 2/06/2013 02:01:00 AM  

  • Wonderful details. You've improved upon my own knowledge within this matter.
    stock market courses

    By Blogger stmoneymantra, at 2/08/2013 04:24:00 AM  

  • This is so cool. I am such a huge fan of their work. I really am impressed with how much you have worked to make this website so enjoyable. stock market tips

    By Blogger Stock market tips, at 2/25/2013 05:47:00 AM  

  • Really nice information.keep blogging. Mcx Tips

    By Blogger Shifa Mcx, at 2/26/2013 11:33:00 PM  

  • Really nice information.keep blogging. Mcx Tips

    By Blogger Shifa Mcx, at 2/26/2013 11:35:00 PM  

  • Really nice information.keep blogging. Mcx Tips

    By Blogger Shifa Mcx, at 3/07/2013 09:56:00 PM  

  • This exclusive article will answer few very general questions related to SHARE MARKET FREE TIPSshare market like what is the motive of stock exchange,and How to invest in stock market?
    Regards
    BIGPROFITBUZZ TEAM

    By Blogger BIGPROFITBUZZ, at 4/29/2013 05:23:00 AM  

  • Shristocktips is firm related with stock market.We provide tips regarding intraday,f&o,delivery.We also provide jackpot call in which you have to take high risk to gain high profits.Our aim is to give you the best possible returns from shareSHARE MARKET FREE TIPS NSE BSE TIPSmarket in every phase whether bull or bear.We always go with the trend because trend is the only best friend in the market.If you are with it You are the king of market & if you are against it then it will make you worthless a penny.So no matter you join us or not but always follow the trend to be in the market for long term.
    Regards
    SHRISTOCKTIPS TEAM

    By Blogger SHRISTOCKTIPS, at 6/04/2013 01:02:00 AM  




  • Thanks for having such information....
    Way2sms login

    By Blogger ExcellentMoney Advisor, at 6/27/2013 02:05:00 AM  

  • This information provided by your blog is very helpful l for New Traders in Stock Market. We too have provided Stock Market Education on our website at http://www.intradaytips.com

    By Blogger INTRADAYTIPS.COM, at 9/03/2013 01:49:00 AM  

  • We are surprised to read information that has been written on this blog
    Intraday stock tips |Nse tips|Nifty tips

    By Blogger Share tips, at 9/26/2013 12:25:00 AM  

  • This comment has been removed by the author.

    By Blogger kavin smith, at 10/18/2013 05:55:00 AM  

  • After getting butchered in last two sessions, the market has opened in green territory. The Sensex is up 92.65 points at 20321.70, and the Nifty is up 29.85 points at 6028.90. About 496 shares have advanced, 104 shares declined, and 16 shares are unchanged.

    Moneycontrol
    Moneycontroltips

    By Blogger PraniShah, at 11/21/2013 09:30:00 PM  

  • Today indian share market is so fluctuating that it is very hard to work & earn money but some companies giving really very good calls so that we can earn in this Share market.PowerofTrading.com is one of them. They are giving regular 5-7 calls with more than 90% accuracy. Go for Free Share Market Tips

    Regards
    POWEROFTRADING TEAM

    By Blogger Powerof TradingCom, at 11/24/2013 11:28:00 PM  

  • Share Market Investment made profitable by SHRISTOCKTIPS- Get NSE/BSE Tips via SMS and Yahoo Messenger. At present market is showing deep consolidation. Some of the stocks performing well & some others are not showing any movementNSE BSE, SHARE TIPSlast but not the least some stocks are making heavy buy at their low consolidation level. So it is very difficult to trade in this type of market with strict stoploss trader has to do the averages only then he can make some profit. So we request all the traders to take our to trial to avoid the loss because in this type of market live support is require to make the trade. We will try our best to make you wealthy & happy. You can visit our website.
    Regards
    SHRISTOCKTIPS TEAM

    By Blogger SHRISTOCKTIPS, at 12/02/2013 02:26:00 AM  

  • Excellent tips. Really useful stuff .Never had an idea about this, will look for more of such informative posts from your side.. Good job...Keep it up Stock Tips

    By Blogger Mcx Tips, at 12/06/2013 09:49:00 PM  

  • sifmr is the best institute for Stock Market training Courses in indore,that educates through extensive courses on capital markets and in its varied dimensions like Technical and Fundamental Research, Derivative and Commodity Markets.

    By Blogger Research 4u, at 12/27/2013 01:32:00 AM  

  • Share Market Investment made profitable by SHRISTOCKTIPS- Get NSE/BSE Tips via SMS and Yahoo Messenger. We give daily stock market tips for future and cash segments. As said last time we made a buy position in NIFTY around 6200-6250 we booked the profit at 6370-6400 level. Our all paid & trial clients made a very handsome profit. Now for the coming week we suggest all the traders to make a sellMCX, STOCK TIPSposition in NIFTY around 6400-6450 for the target of 6200-6100 with stoploss of 6600. Traders can also make a buy position in all NIFTY 50 stocks according to the level of NIFTY. Please trade with strict stoploss because there may be a deep consolidation in the market. For Further update keep following our website & you can also avail our two days trial to check our accuracy.
    Regards
    SHRISTOCKTIPS TEAM

    By Blogger SHRISTOCKTIPS, at 1/30/2014 01:58:00 AM  

  • Heya just wanted to give you a quick heads up and let you know a few of the images aren't loading properly. I'm not sure why but I think its a linking issue. I've tried it in two different browsers and both show the same results.
    Stock Trading Tips

    By Blogger rajnish singh, at 1/07/2015 12:48:00 AM  

  • I am impressed by the quality of information on this website. There are a lot of good resources here. Best NCDEX Tips I am sure I will visit this blog again soon.

    By Blogger Bhoomi Desai, at 5/07/2015 02:13:00 AM  

  • Cám ơn bạn bài viết hay quá !!
    ………………………………….
    Hiện nay chúng tôi sắp mở bán dự án Căn hộ cao cấp Silver Star Nhà Bè, với giá cực rẻ chỉ 1ty2/căn 2PN, với đầy đủ tiện ích vượt trội: hồ bơi, trung tâm thương mại, siêu thị, rạp film Galaxy, hứa hẹn sẽ là nơi tốt nhất dành cho bạn. Chính nơi đây sẽ mang đến cho bạn những tiện ích mà không nơi nào mang lại được. Để biết thêm chi tiết xin liên hệ 0932.699.603 (Mr.Phúc) hoặc vào Xem thông tin Căn hộ Silver Star Nhà Bè tại website Căn hộ Silver Star Nhà Bè | Can ho Silver Star Nha Be

    By Blogger phúc nguyễn, at 8/10/2015 02:41:00 AM  

  • With having so much content and articles do you ever run into any issues of plagorism or copyright violation? My site has a lot of unique content I've either written myself or outsourced but it appears a lot of it is popping it up all over the web without my authorization. Do you know any methods to help protect against content from being ripped off? I'd genuinely appreciate it. Intraday Free Equity Trading Tips

    By Blogger Rajnish Singh, at 11/08/2015 11:47:00 PM  

  • With having so much content and articles do you ever run into any issues of plagorism or copyright violation? My site has a lot of unique content I've either written myself or outsourced but it appears a lot of it is popping it up all over the web without my authorization. Do you know any methods to help protect against content from being ripped off? I'd genuinely appreciate it. Intraday Free Equity Trading Tips

    By Blogger Rajnish Singh, at 11/08/2015 11:48:00 PM  

  • We provide best intraday tips,100 accurate intraday trading tips and jackpot share market tips. Call now +91- 8486224486
    intraday tips
    intraday trading tips
    share market tips

    By Blogger Anand Agrawal, at 12/12/2015 02:52:00 AM  

  • free share tipsNice blog of quereies regarding ,, love the article you published

    By Blogger Somya Panchal, at 1/28/2016 10:55:00 PM  

  • We are really grateful for your blog post. You will find a lot of approaches after visiting your post.

    Great work.

    Such a very useful article. Very interesting to read this article.I would like to thank you for the

    efforts you had made for writing this awesome article.
    Sign in to Gmail to access to all Gooogle services. Log in to your account or sign up to create a new

    account
    gmail sign in

    gmail log in

    what's gmail?

    slitherio

    Tank trouble

    happy wheels

    Strike Force Heroes

    In this game, you start at the cavern men's age, then evolve! There is a total of 5 ages, each with its

    units and turrets. Take control of 16 different units and 15 different turrets to defend your base and

    destroy your enemy.
    age of war

    We are really grateful for your blog post. You will find a lot of approaches after visiting your post.

    Great work.
    happy wheels

    strike force heroes

    earn to die

    Fireboy and Watergirl arrived again to the temple in the forest. 2 players together can help them to find

    their way out.
    fire boy and water girl

    By Blogger hường lê, at 5/29/2016 07:35:00 PM  

  • "For this, I had been using this and it's great.
    It has instructions which can help you.
    Stock Quotes in Excel
    Just visit the site for more info.
    They also have a live support and a forum to help you with your questions.

    By Blogger FlyHigh, at 6/12/2016 07:40:00 PM  

  • Very impressive post. It conveys very useful and helpful information. Traders must rely on good stock tips for trading.

    By Blogger aditi, at 6/13/2016 03:37:00 AM  

  • Welcomes to google terminal from Austin Texas! google snake
    Amazing insight you have on this, Happy wheels it's nice to find a website that details so much information about different artists... Age of war 2 This article always blew me... Earn to die For how many times I have read this.
    slither io Hi! I’ve been reading your blog for a while slitherio it's nice to find a website that details so much big farm

    By Blogger Alice Denny, at 6/19/2016 10:49:00 PM  

  • The topic is very interesting and creative. I would like to use this queries to import the data. Stock Tips

    By Blogger Ram Katariya, at 7/12/2016 02:50:00 AM  

Post a Comment

<< Home