Importing External Data Into Excel on Mac // Excel Tutorial
Вставка
- Опубліковано 18 січ 2020
- In this short Excel tutorial I'm going to show you how to import external data into Excel. This guide is focused on Mac OS since the workflow in windows is a little different.
If you have any questions, please feel free to write a comment and ask directly. I'll do my best to help.
Under the "Data" menu item, I go to "Get external data" item in the dropdown menu, but in the submenu, there IS NO "Run saved query" option, only "run web query," with the two choices below it of "From database" and "Import text file." I tried choosing both of those options and selecting the .iqy file, but it does not work. It's really frustrating that I can't find a tutorial that works. I've tried several, and I finally figured out they're using Excel for Windows, and that it's not even possible in Mac. But I found this tutorial, and thought this would work. It doesn't. I don't know what on Earth the problem could be with this tutorial, because the person is using the EXACT SAME version of Excel I'm using on my Mac, and the menu items I'm seeing him choosing don't exist either.
I have the same problem
Did you solved the problem? If yes. how?
Same problem over here
Anybody figure it out?
Find the file that you saved with the .igy extension. Right click it and select 'Get Info'. Navigate to the 'Name and Extension' tab and I suspect that you have to delete the '.txt' suffix. Once you do, the OS will ask you what sort of file type you want to save the file as--.txt or .iqy. Select '.iqy'. Then open up a blank excel sheet, go to data, get external data, and select 'Run Web Query'. Once you find you .iqy file, you should be able to select it and everything should work smoothly.
Cheers.
2:24 -> For those who aren’t able to select the (grey) iqy-file. Try to save this file as ms-dos instead of mac os.
Nope. didn't work either :(
Nice vid, i've been trying to find a easy solution and here it is. if you are having the same problem that excel wont recognize it as a iqy just do this (This is an extract of someone else comments) :
""Find the file that you saved with the .igy extension. Right click it and select 'Get Info'. Navigate to the 'Name and Extension' tab and I suspect that you have to delete the '.txt' suffix. Once you do, the OS will ask you what sort of file type you want to save the file as--.txt or .iqy. Select '.iqy'.""
Worked a charm, very clear explanation! Thankyou!!
no run saved query option, version 16.42
Wow, that's so useful! Great explanations!
3:20 > for people cannot upload the ipy file. I found the answer from" Webbme - Hemsidor & Media"
Quoted : " click the file and click "show info". There my file name was .iqy.txt so I needed to remove .txt again and then it worked for me!"
For everyone who is struggling doing this, this tip is a really good help and helped me. Now it works.
it worked for me! thanks
Thank you bro, finally I can do it with the method you said
THANKS A LOT MAN , BIG STRUGGLE BEFORE
Thank I have learnt a little bit more but it does not work for me. I copy the link, put it in a word document save as you said and then when I want to put it in excel the document is grey, I have no access but I have 2 queries which were on my computer as samples and they are also iqy ! I have tried again but it does not work...... I have done exactly as you said (txt and before saving iqy).....
me too :/
It worked beautifully. I could select the text file by pretending to rename it and then I was able to import it into excel.
I wonder why did the MS guys make this process so complcated, as this is definitely not a user-friendly way to import a webpage. It is more like an easter egg. Whatever, thank you!
Big up to this guy. Ya legend. Worked a treat.
Thank you, very helpful
Hi Thanks for your video, It's really helpful. I am importing crypto data from the api, the cell I need displays a $ sign as well as the current updated total so my calcs don't work when copied over to my work sheet, Is there any way around this ?
This is a little strange but after looking through the comments I actually solved the issues (sort of).
My first issue was that I can't get the option "Run Saved Query" when I'm going to Toolbar->Data>Get External Data->?
I do however get the options to either "Run Web Query", "From Database" or "Import Text File". If I then choose "Run Web Query" I can run the file I have prepared as shown in your video but by default, unfortunately, there is an issue that some other commenters also was getting namely that the file gets saved as "iqy.txt". If going back to the location where the file was saved (in my case by going to Finder->Applications and then search for "Queries") it was quickly changed and the file could be run.
However, my second problem was that my test website wouldn't work as I only get a message saying that there was no data to import. After testing a few other websites it worked flawlessly so my conclusion is that some websites probably protect their information to prevent this action.
Felt like this explanation probably help to solve some of the issues others are struggling with despite a great tutorial!
I as well don't have the option "Run Saved Query" and only have "Run Web Query". After I press "Run Web Query" I am unable to select my file in the browser window.
@@andrewdahawk I have the same issue. Have you managed to solve this?
Rashvin Singh no, unfortunately. I still import the data manually.
@@andrewdahawk I figured it out. Go to finder and to the folder you saved the .iqy file in. Click "Get Info". Despite initially saving the file as .iqy, there will be a .txt at the end. Delete that part and it prompts you to confirm you want to save it as .iqy It should work then! Hope that helped!
Rashvin Singh thanks! I’ll have to try this out
If its not working on mac, it is probably because your file name didn't change fully.
For example, even when you add .iqy, there may be .txt added to the end without you realizing it.
Go into terminal and modify the file name. So if its SampleFile.iqy.txt, you have to run this -
mv SampleFile.iqy.txt SampleFile.iqy
Hope that helps. Just worked for me
If you wanna change the name of file easily , you choose the file and push command + i and then you can change the name without the terminal
Why do I choose data in the IQY format in Excel, data like in the video at minute 3:06? Cannot be selected.
Many thanks!
I dont see run saved query but i can see only run web query. how can I fix this?
when I save the document on word I use the scroll down window and hit .txt but there is no where to delete the .txt and put .iqy. It only lets me change the name. Can someone please help?
Great tutorial. Can this be used to pull data from another Excel workbook located on OneDrive? I`ve tried but getting alert "This Web query returned no data." Any advice? Thanks!
Hey hjenei, yes this should technically work, however, I am not certain. Do you need to have the files saved in a cloud? I can take a look at it. Best regards
plzzz can you do it again with current version
Hi, leider habe ich das gleiche Problem wie die meisten hier. Bei mir fehlt "run web query". Ich habe Excel Version 16.48, Microsoft 365-Abo.
Ist es wichtig wo man die .iqy-Datei speichert? Ich habe sie aufm Desktop gespeichert. ICh hoffe sehr Du kannst helfen. Danke.
Hallo LebenWerden. Es spielt keine Rolle wo diese Datein abgespeichert werden. Schau doch ob die datei wirklich als .iqy gespeichert wurde oder als .txt.iqy. Sollte das der Fall sein einfach das .txt löschen. Ich hoffe ich konnte helfen!
it doesn't work for me as well, seems like we all have the same problem with the word 2019.
is there another way too save the file a iqy file?
Hey Troels, yes absolutely. You should be able to use any text editor that is able to recognize a link, i.e. Pages, Openoffice, older versions of word etc. Just save it as an .iqy there. Hope it solves your problem!
I solved it by right click the file and click "show info". There my file name was .iqy.txt so I needed to remove .txt again and then it worked for me!
Mine wouldn’t let me click on the iqy document
In case excel just won't open up the .iqy file you saved, try opening up one of the already existing sample queries located in the 'Queries' folder and simply replacing the stuff in there with your link. Then open up the file with excel just like shown in the video.
Worked for me!
why i can't choose End line with CR only?
the .iqy file is grayed and I cannot select it. The other difference is that in excel it says "Run Web Query" not the saved query option that you had. Would that be affecting this?
Hi Elena! That is quite unusual, which version of Excel are you using if I may ask?
I solved it by right click the file and click "show info". There my file name was .iqy.txt so I needed to remove .txt again and then it worked for me!
@@FinanceNik We are using the recent version of MS excel. I have the same issue also
did you manage to fix it or not? same problem here
@@moniquefrogoso5585
@@gabriellagebacha4883 I already did. I used my windows pc to convert all my .iqy files to .xlsx
Nik I have msexcel version 16 on my mac. Please help me on how to get specific data from a different tab in the same workbook. Thanks
Hey! So you want to reference a cell in the same workbook but in a different worksheet, am I understanding you correctly?
@@FinanceNik yes nik. I want to capture data from a specific cell in a different tab and autopopulate that data in a different tab, all in the same workbook !
Iam use a mac air excel version 16
I have been told that this feature power query is not yet available to Mac users. Is that Right ?
@@shawnpenn Actually, as far as I know you can just import the data like seen in this video and reference the cell in a different workbook by the following comment (just need to change sheetname and Range) - Sheets("Destinationsheet").Range("A1").Value = Sheets("Datasheet").Range("A1").Value. Not quite sure if this is what you are looking for
@@FinanceNik pls send your email i will send you my query and the excel doc I want this to work on.
@@shawnpenn If you dont mind, write your email into the comment section and I will contact you
HI, I can't get the LEFT formula to work, im trying to eliminate a dollar sign, is there another way of doing it ?
Query file dimmed, cant select it
Why doesn’t work for me .I do the same but nothing happens
Hey Orisnely, could you specify when your problem arises exactly? Maybe I'd be able to help you. Cheers!
Hi, for me everything works, except that my file generates the link instead of a table. Trying to import information from CoinGecko API. First I thought I didn't save it as a Hyperlink but this wasnt the case. Any help is much appreciated!
I too have this problem, were you able to solve it?
Hi, in case you haven't solved this, i managed to get it to work by using the apple ribbon/data icon/ get external data, rather than the excel ribbon/data/get external data.
@@douglasgaylor4810 Hey still not working for me gives a http fault could you help me? having trouble finding out using google
how to make data connection from another workbook /sheet as windows does selecting "existing connection"
mine is 2019 excel and I don't see such function on Mac , is that disable for Mac?
Hey Mohamed, I am not quite sure if that is natively possible via a function but what you could do is to get the data in one workbook and then just reference the cell in another workbook, so that Excel connects to the other workbook automatically
My excel 2019 just printed the link of the website in the cell despite me following through the tutorial, can someone please help? Thank you!
Hey Weng, are you certain that you double-clicked on the link in the word file so that it was recognized as a link?
It works perfectly for one day but then the stock price changes to another cell and I then have to change it manually. Is there a way to solve this ? Thanks !
Hey, thanks for the video. just in case do you know how to change the format if the link that I want to import is ended with CSV, so the datas won't be all in one column? Because the window to change the format (tab to commas)doesn't pop up when I run "get external data" on excel... thanks a lot!
Hey Jiang, I will have a look at it!
Thanks, this is a very useful video and the comments helped to solve the extension issue. Would the data still refresh if I were to send the excel file to another user? Or it'll be better to use a shared cloud folder eg one drive?
Figured out the solution : send both iqy and xls files to other users. As long as they don’t rename and enable content in excel, the data will refresh.
Could you please make a video showcasing how to time the script to update the data automatically? Thank you so much, incredibly helpful
Hey @Jack, do you mean that the script automatically updates after, say, 10 Minutes or so?
@@FinanceNik Yes! Really any time length would be fine, as long as it pulls the live stats from the website as they update...if that makes sense. I want to do this so bad but I do not know where to even start. Thank you!!
@@jackconnally9291 Okay, if I have time tomorrow, I'll do it asap!
@@FinanceNik You are the absolute best. Please let me know of other social media accounts so I can help you grow and share your stuff!
@@jackconnally9291 Hey Jack, the Video should now be online - sorry for the delay!
hi I want to know if you can automate this process instead of clicking refresh on a Mac
Hi Akshay, yes you could definitely do that. One example would be to insert the refreshing function into a timer. There is a VBA-function to call the current time and then set a date or time equal to the time at which you want to refresh the data. The current time function would then be run every second or so in order to check the time and if, at some point, the current time matches the time set to the refreshing script it would run. Hope this helped. If you have any other questions, feel free to let me know.
Danke für das Tutorial. (Y) Kann man in die Word Datei auch mehrere Links speichern, von denen die Infos dann automatisch mit eingefügt werden ?
Leider soweit ich weiß nicht
It does not work for me, I can not select the file after I made it in word like you did.. :( Tip?
Same....
Hey Sigmund, which version of Excel are you using?
I solved it by right click the file and click "show info". There my file name was .iqy.txt so I needed to remove .txt again and then it worked for me!
@@webbme-hemsidormedia9694 thank you so much ! you are goated !
Hi. Will it work if I share the file with someone (different computer)? Thank you
Hey Gautam, yes it should be working too if you send the file to someone. He or she may want to click on the "refresh data" tab under 'data' for it to display the most recent information. If you want the other person to use exactly the data you have used, you can also copy the whole sheet range (by clicking in the top left to the columns) and then to paste-special - only values. This will save the data in the sheet so that it will not update anymore. Hope this helps
I saved it as plain text and I added .Iqy like you said but it still dose not opean
Hi Salyem, what happens if you press "Run Saved Query" under the Data-Tab?
I solved it by right click the file and click "show info". There my file name was .iqy.txt so I needed to remove .txt again and then it worked for me!
@@webbme-hemsidormedia9694 Thanks it worked
Hi, Thanks for the video. I can get it to update on sheet 1 but the value doesn't multiply so put all my calcs out. Im running Mac OS Catalina Version 10:15:7. I get this massage when I try your cheat at the end. There's a problem with this formula.
do you know how to do it ?
@@stijnv7764 Hi, There is a dollar sign in the cell that uploads so my calcs don't recognise the values.
Hi Nik! I follow the comments and change de extension to .iqy and it loads ok in the sheet. The problem that I have is that all the information it’s being shown in the same cell, it’s separated by “,” but when trying to use the function “text to columns” it gives a lot of columns in the same row, and the information is messy and between quotes “… I’m trying to connect the Binance api to track spot prices of my portfolio. If you could help me would be great, I search like 6 hours in every video or website you could imagine but couldn’t find the solution. And seems pretty easy to solve, already got the information imported just need to make a readable table with it. Thanks a lot!
Ps: I’m using this api in a spreadsheet in windows without a problem. It gives you the option to load the information in a table with the parameters you want.
Hey Tom! You might be able to automise the formatting of the output that you are getting. If you get, say 40 columns every time, than try and make a macro that is putting the columns into rows and then separating the values from the text etc. They way you imported it just gives "dirty" data and there is no easy way to change that other than by using a different data import source. If you avidly try to get better you might be satisfied to hear that it is possible to connect Excel directly to the Binance API, however, you need to code such a thing in VBA. I actually have a video already on how to connect to an API but I did it in Python, try to have a look on the channel. I hope this helps. I will definetly get to make a video on connecting Excel to an API too, however, I am currently in my exam season and find little to no time for videos for the next two weeks. Cheers mate!
@@FinanceNik thanks for your answer! I saw that you have a video about API and python but to be honest it’s too much for me, I’m just a regular excel user. Saying that the API thing I did in windows was an achievement for me. So when I change to Mac a few days ago it was so frustrating to see that I lost that. Definitely would love to see a video of how to do it in Excel. Best wishes in your exams Nik!! Talk to you soon.
@@sadfsadfadadadsada Definetly, I`ll keep it in mind and try and post a video about it as soon as I can, cheers!
Thanks for your excellent guidance. However Excel for Mac is a very mediocre programme and extremely frustrating to work with. I have done it and managed to solve the IQY issues. However it only copies part of the webpage with important info missing. It also creates larger cells once imported which you can't connect to your sheet. All in all Microsoft really puts the middle finger towards us Mac users!
You really speak to my soul right there. One of the reasons why I do not upload as much anymore is that I switched from using Excel to solely using Python for these kinds of tasks now. You are damn right that microsoft deliberately chooses to give mac users a less than optimal product. There are so many issues with using the office suite or especially VBA on mac. Have you have tried to create a dashboard in VBA with slicers, input forms and more? Well these feature are in large parts not available to mac users the way they seemlessly are on windows, even though they exist for years. Python solves these issues. Although its a vastely different workflow and it requires a lot of new learning, it truly is a canvas for you to paint on. I strongly recommend trying something new and ditching microsoft office on mac. Sorry for that rant but your comment truly resonated with me. Have a good week!
I really need input to make the .iqy works because I still cannot make it work
Any idea what to do if the website has a password? I see that there are ways to get around this in windows, but I cant find anything on Mac.
Hey Michael, very good question. Usually one specifies a password within a variable and then pass the variable while trying to connect. As this clearly would not work here I am quite certain that you can only access non-password protected websites with this technique. Looking around the internet I was also not able to find a solution. Sorry mate!
Is there any way to get the day to day data?
Hi Rutvij, yes absolutely! You can time the script in order to automatically update the data every day at a specific time. If you need help with that just let me know and I'll make a video about it. Best regards
@@FinanceNik Thank you so much for the video! I would love a video about timing the script to automatically update the data. Appreciate ya!
Thank you for this great explanation, I am trying to do this with currency, for a certain amount to be multiplied to a currency from a sheet I created following these steps, however, when I try to multiple for example what is 20$ in EUR, it is showing me the VALUE error. Do you know why? I am stuck :(
Hey Carlos! Most likely its because the characters in the cell are not recognizes as number. Is it perhaps that the currency sign is within the cell as a normal letter? Try removing the currency sign or try to get the value in the cell as such: =value(cell you want). Hope this does the trick!
Is there any way I can edit the data that I've retrieved using Power Query in mac? For instance deleting certain columns or rows from the table, etc.
Hey patricia, yes, that is possible, however, you would have to do that in VBA after you run the query
How about more than one text file at once?
Hi @finance Nik, is there any way to run web queries from multiple iqy simultaneously? let say ove 100 iqy's. Thx Nik
As far as I know, that is not possible unfortunately. You have to have multiple sheets to do so
I don't get the option "Run Saved Query".
Hi Saba, may I ask which version of Excel you are using? The cloud version perhaps?
@@FinanceNik Hi I am having this issue too. I am using version 16.47.1 on macOS Big Sur. In the drop down Run Web Query is there instead of Run Saved Query.
Hello, I am not getting "Run Saved Query" option
Hey Lokesh, which version of excel are you using? The bought one or the cloud one?
@@FinanceNik same I’m using the cloud one
I'm using Office for MAC version. After I successfully saved the .txt file as .iqy file, I tried to retrieve it by clicking WEB DATA > RUN WEB QUERY. But then the .iqy file looks "semi-transparent" on finder and not available for me to open it. Is there anything I can do? Please help. Thanks!
I just solved the problem by reading several comments. Then I realised many ppl just got similar case. Thanks them all for sharing solutions!
this isn't working now :(
Very true, on the newer versions of Excel on Mac, this is no longer the practice. I have, however, made a video on how to do it on the newer versions. It's called how to import stock data.
so when I put in '.iqy' it saves it as a txt.iqy
also I don't have the option of 'run saved data' all I get is 'run web query' and everything under that.
when I click run web query I can not open what I saved because it isn't a query. is there any way you can contact me personally and help?
You should be able to just delete the .txt string from the end of the file. Did that help?
Hi, thanks for this video, I am doing the same with crypto data, and when importing it from the api, I get data (name of the asset + value) in the same column. Is it possible to import it directly in 2 different column ? otherwise I'm running a second macro to rearrange the data and everytime it blocks because it asks me if I want to replace the existing data in the column, and I can't find the way to avoid that. Could you help ? I can send you the data if needed. thanks
There is definetly a fix for that. As fas as I know you have to use the second macro, however you can give the second macro the properties of ignoring "warning boxes". Personally, I did not do that yet but I could try and find a fix for you. Best regards Nik
Trying to do the same with crypto what API key do you use? If you would help me a bit would be great
2.3300 x 0
what formula i need insert to get a proper number?
"=LEFT(Cellnumber, 5)"
how do i import videos into excel in mac os?
Hey Shine_uno, what exactly are you going to do with the video? Do you just want to hyperlink within a spreadsheet or actually watch it within excel?
@@FinanceNik hello thanks for your answer. yep i would like to load the video within the excel . thanks
@@shine_uno I found out how it works but the workflow is a little too cumbersome to explain via a comment. I'll just make a video about it within the next couple of days
@@FinanceNik nik. i love you! thank one more time!
@@shine_uno Ohhh sorry, I totally forgot to put that on my to-do list. Im sorry!
I can't find "Run Saved Query"... if I click on Data - Get External Data there are only other things but not "Run Saved Query"
Hi Flemir, which version of Excel are you using at the moment? I might be able to find a fix. BR
@@FinanceNik I have the same issue. I am running Excel 16.40 on mac
Did you solved the problem? If yes. how?
omg
This didn’t help 🥴
Hey Juan, can you explain why it did not work for you?