Excel for Mac: How to Import Data From a Web Page
Вставка
- Опубліковано 16 сер 2024
- Use a Mac? Need to import data from the Web into Excel? Power Query would seem the obvious way to do it however, unlike Power Query in Excel on Windows, Power Query in Excel for Mac doesn't currently support importing from a web page. In this video I show you a workaround.
✅ Want more FREE training?
Why not check out my other tutorials and/or subscribe to my newsletter, both of which can be done at theexceltraine...
🙋♂️ Let's connect on social
Twitter: / theexceltrainer
Facebook: / theexceltrainer
LinkedIn: / thomasmik
#excel #mac #importfromweb
Thank you so much sir. When you are on Mac, It is very frustrating to follow Excel explained on Windows as half of the functions/buttons are missing from Mac and most of the time in PQ. Loved the way you explained it so clearly. Saved the day. I hope Microsoft can figure out a way to include Data Model, DAX and Mcode in Mac too. If there is any way to use them on Mac, please do made a video on that too. Will really appreciate it.
Although Excel for Mac doesn't support DAX, you can use M code in the Query Editor on a Mac
works! You can refer to imported data from other cell and format it there and leave your imported data somewhere on the side.
Another very useful (and excel-lent) trick from the maestro of macros 🐼
thank you!! no idea why they make these things so difficult.
Thank you so much for this. I found it helpful, and it worked. This was the only help I found that worked.
Great to hear!
Hello Sir! I've watched your video, the skill is really practical. I bought a Macbook for five years and I just learned this function after having watched your video, thanks a lot! Could you tell me how to create a Macro file to keep the format. I really appreciate that.
I was looking everywhere on how to get web details to excel for mac. This really helps a lot. Thank you so much!!
Glad it was helpful!
Thank you sir, taking a course rn, prof uses Windows, I don't, you saved me here!
Glad I could help!
Thank you, I've been looking for this for months.
Glad I could help
This was really helpful.
you have saved my uni assignment
WOW I was lost!! Thank you so much you helped tremendously!!!
Excel-lent!!!!!!!!!! Thank you for sharing Mike!!!!!!!!
My pleasure!
Thank you boss 🙏 it really helped me a lot with my fantasy premier league team and other gaming ventures
Glad it helped
Golden! Thank you.
Brilliant!!! Thank you so much.
Thanks!
Thank you so much!
Thanks so much
Thank you so much Mike your the best!!!!!
Nice! Thank you!
Mactastic! Thanks Mike
You are welcome!
Great Video! Thanks for sharing! Also, can you post a video explaining how to use text function on mac excel to split a date up into two columns, such as month and year? { (For year, = TEXT ( date_column, "YYYY")
For month, = TEXT ( date_column, "mm") }. I am having trouble doing this for an assignment in which I have to make a dashboard that tracks the unemployment rate.
Genius! Thank you.
You're welcome!
This works great, and I've been trying to figure this out for some time. I did find one issue I'm not sure of, once I import the data from the web and I try to "Refresh" as in your video, the Refresh function is greyed out ? thanks for a great solution
Just tried using this method and it worked for one website but not another. For the failed URL, I receive this following error message: Cannot locate the Internet server or proxy server. For what it's worth the webpage shows the data in a table that is filterable by many characteristics. Any idea on how to get past this error to get the underlying data? Thanks.
çok teşekkür ediyorum.😊
Hi Mike, i've watched your tutorial and i've found it very useful. I'm working on stock price series getting data from Yahoo Finance. When I import the url of the web page of the S&P 500 Index for instance, the data i download on excel are in CSV format and not excel. Is there a way that you know to download them in xlsx format? Thanks very much for your help and hope to join your channel again!
Can you explain with more detail how to do the macro after updating in order to have all the adjustments done again? Thanks in advance, sir!
@user-eq7zn5hh3g Check out this video that I created on how to create macros: ua-cam.com/video/9IEXuNFS14Y/v-deo.html
Thanks this is very helpful. If you wanted not the whole data set, but one data point from it, how would you build that into the query?
@user-vb9ex7zr1b import the data as per the video. Then user Power Query (Data > Get Data > Excel workbook) to apply a filter...or...just apply a filter using standard Excel filtering once the data has been imported
Thank you, Mike. I'm interested in being able to do exactly as you describe, but for websites requiring an account with username and password.
@jeffelbelping1844 I don't think that's possible, certainly not using the method in the video.
@@MikeThomas67 Thank you for the feedback, Mike. That's consistent with what I've read elsewhere.
Thanks for your help! :) you have a new French subscriber who lives in Mexico. Is it normal that I cannot import certain pages from a website or that certain pages while the list does not change order, when updating the data on Excel the order of the teams changes? I don't know if I was understanding.
@DoradoParisSportifs Hi and thank you for subscribing. You are right there will be I'm sure some web pages and sites that this just won't work with. Regarding updating the list, what I was saying was if the data changes on the website then you have to Refresh but if you do any formatting that you have applied in Excel is lost because refresh reloads the webpage into Excel and over writes what was there
Thank you for this video. It has been most useful. I just liked and subscribed. I had a question though. Does it have to be formatted into a table to be able to refresh it? Can you use multiple links in one iqy file? Do you have any advice or videos for how to create macros and get the formatting to update right after the upload of the data? I work on files with sports data that includes importing data from many different websites and I am trying to avoid getting a windows pc or using parallels and buying a windows key just to be able to use this feature. Any help would be greatly appreciated. Thanks again.
@billbrennan9619 Thank you so much for liking and subscribing! It doesn't have to be formatted as a Table. If you right click on any cell in the imported data there should be a refresh option. Also Data > Refresh All works.
IQY files as far as I can tell can only contain a single URL however you can import multiple IQY files into the same Excel file. Import each one separately and specify the location where you want the data to go
On your question "creating macros and get the formatting to update right after the upload of the data" I have a video about creating a macro but it don't focus specifically on your situation: ua-cam.com/video/9IEXuNFS14Y/v-deo.html
Thanks, but for some reason, not possible to uncheck "if no extension is provided, use "txt". Simply wont let me uncheck it..
god bless your soul
get this error while importing .iqy file-"The required Internet protocol is not installed on your computer". Please suggest next steps.
@richasharma2907 What version of Excel do you have?
What if the API / Website reuires Login details - any idea for this?
@gaspershut I dont think it's possible. You might be able to do it using VBA but not using the method I showed in the video
Great video. Another related question. Is it possible to get external data from another excel table hosted in OneDrive? I know on Windows it does, but can I in MacOS?
@webertbrito You can't do it using the method I used in this video. I tried and it doesn't recognise the URL in the IQY file. However if you have the OneDrive app installed, you have a OneDrive folder in the Finder - usually it's inside the "user>your name" folder but this is configurable. This folder contains "pointers" that point to the files in the "real" (i.e. Cloud-based) OneDrive. Use Power Query to pull in the data (Data > Get Data > Excel Workbook)
Hi Mike,
I have tried this. However, all it does for me is just import the URL into an Excel file. I really do not know what I am doing wrong or why it just does not work. Can you help?
@hoojees What is the URL?
Thia was very helpful thank you but still one question, i dont have the "run web query" option
@user-pw8hu2wb2u what version of Excel do you have? Click Excel (top of screen next to the Apple Icon) and select About Excel
Thanks for your help Mike, I have been trying to do this for ages! However, when I import a table (crypto prices) it only imports all the table data for the first 20 rows and after that just the data in the first column....any ideas please?
@user-yz7ds1oz7z Sounds like it could be the way the data is structured. Feel free to post the link here or drop me a mail via theexceltrainer.co.uk/contact/
if i save links as iqy, i cannot open them in excel. they are just grey...:(
@X90Chris How did you create them? Depending on the application you used, you might find .txt has been added on to the filename as an extension. Check out this link: stackoverflow.com/questions/66209394/excel-on-mac-cannot-open-iqy-file-in-data-get-external-data-run-web-query
Could this work in google sheets on Mac?
@Boobye23 I'm not a Google Sheets user but this page seems to suggest that you can import data. It's done in a different way to Excel: www.softr.io/google-sheets/formulas/importdata/r/WR8DWMNhzZFRnEgJreGHnK
Sir. Thank you for this excellent video. I have a question, though. The data I am trying to transpose into an excel spreadsheet is coming from a webpage which requires logging into it (it's not available freely in the web). I think this is the reason excel is not accepting the transposition, because it says "internet sever or proxy could not be located". Could you please tell me if there is a way around this? Thank you!
Hi @mdtrindade17 , I don't think this is possible
Hello, thanks for your tutorial. I still have an issue : data are endlessly loading and never got imported. What may I do to solve the problem ?
@louisrobert7057 I've had this with a couple of websites. I normally give it 10-15 seconds and cancel and try importing again. It could be an incompatibility issue between Excel and that particular website. Not all websites can be imported
Hello friends, I have a question. It is a good method to extract data from a single page. What should someone like me who wants to extract data from 540 separate pages do? Can anyone help me on this issue? I THANK YOU VERY MUCH TO OUR VALUABLE FRIEND FOR THE VIDEO.
@CuneytO You would need 540 separate IQY files. Each would need to be imported separately. You could probably automate it with a macro.
@@MikeThomas67 Thank you very much.
You're welcome
Thank you for great video. I am working on Mac, and when I follow all steps, my iqy file is greyed out and I cannot select it ... could you help please?
Hi @yuriygalabura5531. I had the same issue. It LOOKS greyed out but is actually clickable/selectable. Try again and let me know
@@MikeThomas67 thank you Mike, I had figure it out. The problem is that even when we rename the file and add iqy extension, txt extension is added, but i initially is not visible . I changed the preferences in the Finder to see all files extensions. Deleted txt after saving the file and that worked.
Hello Mike.Many thanks for the video. During an attempt to import the data, an error message in Excel occurred: An unexpected error. Could you pls advise?
Hey Mike, is it somehow possible to enter multiple URLs in 1 file to get data in the excel?
@se1to200 unfortunately not
Mike's earlier reply: IQY files as far as I can tell can only contain a single URL however you can import multiple IQY files into the same Excel file. Import each one separately and specify the location where you want the data to go
I couldn't find this solution anywhere. Thanks. I wanted to use it to import quarterly results from stock listed companies but the outcome is just gibberish. Numbers like 0000000012551 in only the A column. So guess this won't work. Thanks anyway