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.
Thank you! Thank you! Been trying to find a work around for a while! My work pc does this so much better, so surprising cuz normally Mac's have the better user interface. Anyway, great video. Easy to follow along step by step! Will be coming back for more deep cut excel tricks!
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.
@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
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
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!
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.
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
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?
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)
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/
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?
@@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.
@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 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?
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: ruclips.net/video/9IEXuNFS14Y/видео.html
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.
The error might be due to restrictions on the website or incorrect URL formatting. Try checking the following: ensure the URL is correct, use headers to mimic a real browser request, or consider using Selenium to interact with the filterable table. Additionally, check for any potential bot protection on the site
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!
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
Additionally, you can use Python libraries like requests for session management to handle logins. Start by sending a POST request with your login credentials to the login endpoint, then maintain the session to access protected pages. For sites with complex logins, consider using Selenium to automate the login process
@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
@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
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.
For dynamic links, you can use a tool like Selenium to automate the browser and interact with the webpage to capture the correct URLs. Alternatively, inspect the page's network requests to identify patterns in the links, then construct your requests accordingly.
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
![Gucci Mane & Sexyy Red - You Don't Love Me [Official Music Video]](http://i.ytimg.com/vi/j7IBqyFKT4c/mqdefault.jpg)
works! You can refer to imported data from other cell and format it there and leave your imported data somewhere on the side.
thank you!! no idea why they make these things so difficult.
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
Thank you so much 🙏
Thank-you so much sir. I have tried lot can’t find anything before your information.
You are welcome!
Thank you! Thank you! Been trying to find a work around for a while! My work pc does this so much better, so surprising cuz normally Mac's have the better user interface. Anyway, great video. Easy to follow along step by step! Will be coming back for more deep cut excel tricks!
@tymccarthy9661 Happy to Help. I have a playlist dedicated to Excel for Mac: ruclips.net/p/PLCefzCWygerI02pC8zCvKuRhF9etmR7dW
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.
Thank you, I've been looking for this for months.
Glad I could help
Hi, I tried everything, I can see the file, but it's greyed out and I can't select the .iqy file. How can I overcome this?
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!
Another very useful (and excel-lent) trick from the maestro of macros 🐼
man... You are a savior sir. Amazing! Thanks a trillion!
you have saved my uni assignment
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!
Thank you sir, taking a course rn, prof uses Windows, I don't, you saved me here!
Glad I could help!
This was really helpful.
Thank you boss 🙏 it really helped me a lot with my fantasy premier league team and other gaming ventures
Glad it helped
Excel-lent!!!!!!!!!! Thank you for sharing Mike!!!!!!!!
My pleasure!
WOW I was lost!! Thank you so much you helped tremendously!!!
Brilliant!!! Thank you so much.
Thanks!
Mactastic! Thanks Mike
You are welcome!
Thank you so much Mike your the best!!!!!
Golden! Thank you.
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
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
Nice! Thank you!
Thanks so much
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!
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.
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: ruclips.net/video/9IEXuNFS14Y/видео.html
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
Genius! Thank you.
You're welcome!
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.
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?
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)
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/
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.
Thanks, but for some reason, not possible to uncheck "if no extension is provided, use "txt". Simply wont let me uncheck it..
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 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?
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: ruclips.net/video/9IEXuNFS14Y/видео.html
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.
The error might be due to restrictions on the website or incorrect URL formatting. Try checking the following: ensure the URL is correct, use headers to mimic a real browser request, or consider using Selenium to interact with the filterable table. Additionally, check for any potential bot protection on the site
çok teşekkür ediyorum.😊
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
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?
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
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
Additionally, you can use Python libraries like requests for session management to handle logins. Start by sending a POST request with your login credentials to the login endpoint, then maintain the session to access protected pages. For sites with complex logins, consider using Selenium to automate the login process
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
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
god bless your soul
what if my link dynamic?
For dynamic links, you can use a tool like Selenium to automate the browser and interact with the webpage to capture the correct URLs. Alternatively, inspect the page's network requests to identify patterns in the links, then construct your requests accordingly.
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
Thank you so much!