MS Excel - Import Live Data From Web to Excel

hi this video will be looking for how do we import data from different resources like from the internet that's a web-based and also we'll be looking for how to import from notepad let's see the first one suppose you want to get the live cricket scores in your excel without opening the browser this is possible you want to check the share values for Finance in your excel without opening opening the browser it is also possible let's see that how we can do for that you need to have some website URL which is changing dynamically let's see that now if I open a browser I have some website name that's a time and date calm and if you see the current time which is working right now it's certain 1920 156 seconds this number this time is changing automatically if you see what I'll do is I want to change this data I want to get this data into my excel so first thing is you should have the URL I'll just copy the URL ctrl C copy the URL ctrl C and I'll just minimize it and now what I'll do is I'll go to some blank sheet where I want to paste it and I can go to this insert tab or I can go to this data tab and you see there's a get external data I can get external data from different resources one is Microsoft Access from web from text so I'll select from web now it will give me a browser this is a private browser for Microsoft Excel I need to paste the URL and have to click on this Go button so first I need to delete this garbage link which is given here and I'll paste using control V that's a website you all which I have and after click on go and it loads it takes time to load here yeah so once it's done so what will happen is you see the scroll so that is the same time 19 23 0 1 which is changing data now so what I can do is I'll just take it up now it says there single line wherever you get this kind of check box you have to click on that click on that check box next to the table you want now some web sites you can get small boxes like this I'll just click on that now if you see this data will be imported into my Excel live data will be imported like this so some websites you get small boxes and you get that kind of ello check box you can select and use it right now I have this one so I have selected the second thing is you have to click on import right I'll just click on this import and it will get imported right after that dialog box it will ask what is the location you want to store do you want to store in this location that is the existing that is dollar a dollar one or else you want to paste on the new worksheet right now I want to paste here because it's a blank worksheet and click on ok and it is getting data from that website so you see that's a data which I've got and I will show you some data which is dynamic here changing data time and date time and date okay and I'll check it there's some time which is changing yeah so I've got that I'll just maximize I got that time which is changing so now if you see the time is 1923 54 that 7:00 p.m. 7:00 37 23 p.m. now now observe this 723 p.m. so if I go to this data tab and if I click on refresh it has an option as a refresh so see the time will change 723 it will change refresh so you see this sum option you'll get it you see this 1923 change to 1924 and 40 again you keep keep on refreshing it will change see 48 so now I have to do it manually is there any setting which I should get live scores as I said or lifetime which is changing now so I can do that so if you keep your cursor you see there's a properties button which is unable if I keep my cursor here that is at this place this properties will be disabled and if I keep my cursor here it is enabled I'll just click on this properties dialog box this dialog box if you see there's written here something as refresh every by default 60 seconds I'll click on that place I do not want I cannot wait for 60 or 60 minutes I have to wait for one minute that's a maximum I can wait and I'll click on ok now the time is 19 24 48 if we just wait for some one minute you see the time automatically changes automatically so same you can get the cricket live scores you know you don't have to go to the browser click on that link and select the country versus which is working or also you can you don't have to go to the share market website you can get everything calculated here itself so every 1 minute it will refresh the data so when it shows you the refresh at the bottom if you see there's a message which will get it getting data from the website somewhere you will get the message here let us see wait for some more time let's say 30 seconds it will show automatically now next topic meanwhile we will go for that from notepad after this we'll be seeing how to get the notepad suppose some person changes some data on the desktop or some location something you have changed automatically in your excel file the data will change so this setting also we can do with the from notepad the option which will be available here that is in this place from text so let us see first yeah so you see the time has changed automatically now it does say this 1926-27 so we waited from one minute and automatically the time changed this is very useful for people like finance background they use a lot they tried to calculate how many shares they have sold what is the profit they have got and so on all the calculations now next is how do I get something from notepad the first thing is I should have a notepad on the desktop or some other location I have some notepad file that's my notepad file and the name of the file is my notepad file double click and open there's one sentence written what is the sentence I've written that's a favorite part of mine a coat of mine that's work in such a way that the presence and absence should be felt what I want is I want to copy this line into my excel and if I keep on changing this data and my accelerate should automatically changed so I'll just close it first remember the name of the file my notepad file and the location is desktop so I'll go back to my excel here that's my excel here and now how do I get it go to data tab click on this get external data and you see there's an option as from text so from text which text you have the location I have the location I'll click on desktop on the desktop that's my notepad file click on notepad file and click on import now you remember that is text to text to columns options where you get a text and you can break in two separate columns the reason is sometimes you don't want forty fifty thousand lines into one cell you want to break it to different columns now I want in the same column I do not want to do anything I just click on this finish and I'll get the same line now it will ask where do you want do you want in the same sheet or do you want in the existing worksheet I'll see in the same sheet click on OK and you see it says the same line I've got it here walk in such a way that your presence absence should be filled now what I want is I I want that if the data changes on the desktop it should change automatically so same if I click right now I go manually and change it there so I'll go to desktop click here and I'll delete entirely work but make sure smart work hard but make sure you also work smart and I'll close it it allows to save saved it so I've changed the proverb let's go back to excel go back to excel in this place yeah now the text is written here the same text do I need to refresh yes I need to refresh it but when i refresh it is asking import a text file why it is asking import X file let's see that if I click on import text file in this notepad file it will work I'll cancel it again if I click on refresh what is happening is if I click on refresh it is asking me which file do you want to refresh so that is not what I want I want my work to be saved so what I'll do is I'll keep my cursor at that location which I've imported data click on properties you see there's an option given as prompt for filename on refresh why should I do that I do not want to you know prompt the dialog box I will remove that option and click on ok that's it and I have to just click on refresh so refresh and the text changes automatically click on refresh automatically takes changes work hard but work smart I'll just you know make auto fit the entire table itself ok so it has divided somehow so it's not there so this is how you can change the data dynamically from text notepad similarly I have some other options which is available here from access from other sources you can do it like this from access from the other sources which are available loads of options are available here so this way now also you can keep a timer here the last thing I'll show you here if you keep on click on properties you can ask them I want to refresh every 1 minute not 60 minutes every 1 minute click on ok so any person who's sitting if it's in the shared Drive Network if they keep on changing automatically you can get an update in the Excel that is interconnected so I think I hope you have understood how to import data from notepad and web-based that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 894,653

Keywords: ms excel - import live data from web, how to import any website live data to excel, how to pull live data from a website into excel, live data from website to excel import live data from web, web to excel, import data, advanced excel, website to excel, excel import data, microsoft excel, import data from website to excel, from web to excel, import web data to excel, excel tutorial, how to import data from web to excel, excel magic tricks

Id: 2yTAyVXzFGg

Length: 9min 20sec (560 seconds)

Published: Mon Jan 15 2018