Thank you Sigma! This JSON parser gave me hard time, I was not sure how to dig through the structure with VBA code. This explained everything! Saved me hours of work, thanks!
Hello! Thank you so much for your perfect video. This is exactly what I was looking for. I am trying to index my 70,000+ digital photos using Azure Computer Vision APIs so that I can search for photos that has sunsets for example, or photos that have birthday cakes. Next step is to train a model to recognize faces of my family so that I can search for photos that contain my wife and my daughter. I will use Access as my database and I was stuck on how to parse the JSON file that I will get back from the API. Your video is super helpful. Thank you so much :)
Hello I have a question. I'm very new in this topic. Is it possible to get data from google sheet to excel using this method and vice versa? I have read that google sheet has an API and it is JSON format. It is possible to do with this method? Thank you.
Hey Sigma thanks for this video - very useful tutorial. You use the Google maps API here, but can you tell me if it's possible to make API requests to return regular Google Search results? Thanks
This is a great example, I have to admit it help me a lot with my project . Is there any chance of you making a video or just a code sample of a json post request? I am trying to update a woocommerce product with the json api. I have recreated the request with insomnia and everything works ok but with vba I dont know how to pass this argument { "regular_price": "58.35"}. Thanks again for the great work.
I need to know if I can make a Api request to a website that requires oauth? I'm still trying to learn this. The code is in json format. Is there no way we can use power query
Nice video. I am trying to find a better method to pull stock data from the alpha vantage API. Currently using Power BI which crashes half the time. Two questions. In the first example if you change "a" to another letter, example "b" you get a "wrong number of arguments or invalid property assignment" error. In the second example I get a "runtime error 424 / Object not defined" at "For Each result in Json("results")". Json is defined as an object. Any thoughts would be appreciated.
Hi Sigma, Great Video . Can we make code a little dynamic for Column Headings input manually. For example we are putting each and every heading from Jason data for writing in excel like ID, Name, Latitude etc. So can that be made dynamic from the request and then make use it in the loop for any sort of headings it receives. As I am thinking to use the code for importing different type of Jason file for API testing . . thanks
This is really great and it has helped me a lot. I am trying to pull stock index data an it worked just fine. Is there a way we could pull json data for multiple urls concurrently. An in my case I use two index data and I am able to pull only 1 at a time so how can we pull for 2 urls and then put into excel?
Hi Sigma! Thanks for the video. It is very informative. I was able to download and parse json data. However, If I try to download complete data, excel starts to not responding. I have started .SetTimeouts 0, 0, 0, 0 and/or waitforresponse but the problem still persist. Any tips? Note: My data contains nearly half a million records. Thanks in advance for your help.
I really need your helping building the url. I have the base url and I have my Api keys. I have 4 pieces of info they provided for the Api key which requires oauth. I just need help making the url right. Once the url works and I can run it in a browser and it returns proper data I should be able to use the url with power query. Are you able to help set this uo
:D !! I am also struggling with oAuth to join the variables containing API keys into the JSON string for the same attempts like you. It's a bit challenging and not sure at this time. But this video is helpful.
Edit: Figured out you need to ReDim with the number of values in the key, ex. ReDim myarray(Json("values").Count, 0) Any advice for storing the values in a temporary array to manipulate without have to put them on a worksheet? I'm wanting to add the values to comboboxes, etc. for use within an add-in for users to be able to pick values that then uses the picked value to grab more JSON data from the API. But I cannot for the life of me get it to store the values into an array without having it use a worksheet as a medium.
Hi, I'm using Excel 2010 and, adding paramters to api requeste, I receive an empty string. If I apiurl?key=mykey I receive response with the xml file content and I can work with that. If I apiurl?page=1&key=mykey I receive an empty response. If I put this request in browser path I receive the xml without problem. I don't undestand why it happens. Can you help me in solving this issue? Thanks
Hi Sigma, great video but I appear to be stuck in the basic steps maybe you could give me some type of hint! I downloaded the son vba on my laptop and I can find it on my desk however when I go on excel and try to import it from my vba it does not seem to appear. If you have any idea this would help me a lot! Thank you in advance
In the video, I had `Option Explicit` on, you need to turn that off so you don't get that error. If you don't you'll need to declare each variable at the top of the code. For example, `Dim base_url As String`.
Thanks for the great tutorial. But i have a problem in json_parseObject at "set json _Parseobject = new Dictionary". I get "invalid use of the key new" . The Ms scripting runtime is already bound. I would appreciate any idea. I reproduced your sample in video 1.1 so i have no explanation what it could be.
how safe is the file you used from github? I'm trying to learn ways to make something similar to this work at my workplace but with all the licensing and stuff, I'm hesitating to possibly use it.
It should be, I haven't had any issues with it. If he gave it an Open Source License, you could use it then. I would just cite where you got it, so if other people want to see the source code they know where to go.
You just use the setRequestHeader method: ' For Content Type. xmlhttp.setRequestHeader "Content-Type", "text/json" ' An example of Basic Auth username = "someusername" password = "somepassword" xmlhttp.setRequestHeader "Authorization", "Basic " + Base64Encode(username + ":" + password)
Hello Sigma, thanks for this informative video! :) I have some few question what if my JSON data it's kind like this: {"results" : { "values": [ [1,2], [3.4] [5,6] ] } I want to get all my data in values, but my values is in Array format not dictionary. How can I get for example the One(1) value in the first Array. Thank you. I hope you notice this! God bless !
@@SigmaCoding Thanks Sigma! I actually found a work around. But I have other problem and I already look around internet if there is work around on my problem, but I got no luck searching for answer. I was hoping that you know the answer, or at least you already encounter this. My API server has been shutdown, and when I request on the excel (and since server is shutdown, it doesn't give response), what happen is Excel is hang up waiting for the response. Is there a way to set timeout on MSXML.xmlhttp60 ? it seems like there is no settings for that. Again, thank you for responsing.
@@henroljeogutierrez1294 Take a look at this post on Stack Overflow that should do the trick. stackoverflow.com/questions/14052543/how-to-set-http-timeout-using-asp
hi there i hope you can h elp me or point me in some kind of direction .. my goal - i wanna grab data from basicly a website in my case the value of my portfolio on a crypto currency service website into excel. they provide that data over api but when i try to connect it always endsup with authorization error. of course i have the public and private key but im unable to find out so far what exactly to do with them. importing public data which doesnt require authorization works fine only this authorizastion i have no idea and for weeks im searching online not finding anything. by looking at what you are doing here i thought it might be worth trying to ask you for advice or a hint maybe you know some place where one could find a example cheet to look at how things are supposed to be done. any kind of help is much aprectiated im desprate by now and dont know what else to do than to start asking people directly for help
Hi there, If you could, please send me the code you already have to my email coding.sigma@gmail.com and a link to the API you're using so I can read the documentation.
Please do a video on this for TD-Think or Swim with VBA! I'm able to establish a successful 200 code connection but, my parsing returns nothing! VBA will allow people to skip python>pandas>excel process!. Thanks!
Just an FYI, it'll be hard to handle all the factors related to authentication using VBA. Not to say it can't be done, but it might take a significant amount of effort. I had a client who wanted to use the TD API from VBA, and my solution to him was writing a COM Server in Python that controlled the TD API Library and then have it be ingested by VBA. I call it a happy medium solution. You can still get VBA, but you also have the complicated parts in Python. Would you be interested in a topic like that?
@@SigmaCoding Ya that would be awesome!!! Btw any idea why TD ameritrade's API can't get real time qoutes for Forex? it seems to work fine for everything else...
@@slave2karma In my adventures of trying to access TDA API for forex it appears that the only way is to use their streaming API to access forex history prices which is probably what you want. They have sample code written in javascript for it but like Sigma said it looks like it would be a bear to emulate that code in vba. I've had some success using Excel's RTD function to access forex prices but in order for that to work for anything really useful it appears to me that you would have to create your own history DB and port price changes to records in one minute increments. I've managed to get live real data pricing but it's next to useless without a history of price changes. FWIW.
Thank you Sigma! This JSON parser gave me hard time, I was not sure how to dig through the structure with VBA code. This explained everything! Saved me hours of work, thanks!
Hi do you have a similar example loading into an Access database table?
Hello!
Thank you so much for your perfect video. This is exactly what I was looking for.
I am trying to index my 70,000+ digital photos using Azure Computer Vision APIs so that I can search for photos that has sunsets for example, or photos that have birthday cakes.
Next step is to train a model to recognize faces of my family so that I can search for photos that contain my wife and my daughter.
I will use Access as my database and I was stuck on how to parse the JSON file that I will get back from the API. Your video is super helpful. Thank you so much :)
Hello sir. I would like to ask if you were able to connect excel to access using this method?
Great stuff. Any reason why you don't write Dim xml_obj as NEW MSXML2... rather than setting it on a separate line?
I think it's easier to parse a json file with the split function. Nice tutorial!.
Hello I have a question. I'm very new in this topic. Is it possible to get data from google sheet to excel using this method and vice versa? I have read that google sheet has an API and it is JSON format. It is possible to do with this method? Thank you.
Hey Sigma thanks for this video - very useful tutorial. You use the Google maps API here, but can you tell me if it's possible to make API requests to return regular Google Search results?
Thanks
Yes.. Sydney Australia.. and YES.. they are good... been to 2 of them .. :) - great vid by the way..
This is a great example, I have to admit it help me a lot with my project . Is there any chance of you making a video or just a code sample of a json post request? I am trying to update a woocommerce product with the json api. I have recreated the request with insomnia and everything works ok but with vba I dont know how to pass this argument { "regular_price": "58.35"}. Thanks again for the great work.
I need to know if I can make a Api request to a website that requires oauth? I'm still trying to learn this. The code is in json format. Is there no way we can use power query
Nice video. I am trying to find a better method to pull stock data from the alpha vantage API. Currently using Power BI which crashes half the time.
Two questions.
In the first example if you change "a" to another letter, example "b" you get a "wrong number of arguments or invalid property assignment" error.
In the second example I get a "runtime error 424 / Object not defined" at "For Each result in Json("results")". Json is defined as an object.
Any thoughts would be appreciated.
Did you copy over the JSON source code into a separate module? It sounds like it can find the object.
@@SigmaCoding JSON source code is in a separate module.
Hi Eric, I got task to POST a JSON formated excel data through a API and get response. Can we do it with EXCEL POWER QUERY?
Amazing tutorial 🙌
Great instruction for API newbie. Thanks.
Glad you enjoyed it!
This is the great video. Thank you.
Hi Sigma, Great Video . Can we make code a little dynamic for Column Headings input manually. For example we are putting each and every heading from Jason data for writing in excel like ID, Name, Latitude etc. So can that be made dynamic from the request and then make use it in the loop for any sort of headings it receives. As I am thinking to use the code for importing different type of Jason file for API testing . . thanks
My API key is invalid, what should I put in Worksheets("api_key").Range("A1")?
Thanks for this, it was really useful
Glad it was helpful!
This is really great and it has helped me a lot. I am trying to pull stock index data an it worked just fine. Is there a way we could pull json data for multiple urls concurrently. An in my case I use two index data and I am able to pull only 1 at a time so how can we pull for 2 urls and then put into excel?
Hi Sigma! Thanks for the video. It is very informative. I was able to download and parse json data. However, If I try to download complete data, excel starts to not responding. I have started .SetTimeouts 0, 0, 0, 0 and/or waitforresponse but the problem still persist. Any tips? Note: My data contains nearly half a million records. Thanks in advance for your help.
I really need your helping building the url. I have the base url and I have my Api keys. I have 4 pieces of info they provided for the Api key which requires oauth. I just need help making the url right. Once the url works and I can run it in a browser and it returns proper data I should be able to use the url with power query. Are you able to help set this uo
:D !! I am also struggling with oAuth to join the variables containing API keys into the JSON string for the same attempts like you. It's a bit challenging and not sure at this time. But this video is helpful.
hey sigma i have a question for you is there any way to talk to you direct as i would like to learn how its done so i can do it for other things
Edit: Figured out you need to ReDim with the number of values in the key, ex. ReDim myarray(Json("values").Count, 0)
Any advice for storing the values in a temporary array to manipulate without have to put them on a worksheet? I'm wanting to add the values to comboboxes, etc. for use within an add-in for users to be able to pick values that then uses the picked value to grab more JSON data from the API. But I cannot for the life of me get it to store the values into an array without having it use a worksheet as a medium.
I need to know that how can we add a body content in REST API call using VBA like .SetRequestHeader "body", "grant_type:client_credentials"!!!
Hi, I'm using Excel 2010 and, adding paramters to api requeste, I receive an empty string.
If I apiurl?key=mykey I receive response with the xml file content and I can work with that.
If I apiurl?page=1&key=mykey I receive an empty response. If I put this request in browser path I receive the xml without problem.
I don't undestand why it happens.
Can you help me in solving this issue?
Thanks
Hi Sigma, great video but I appear to be stuck in the basic steps maybe you could give me some type of hint! I downloaded the son vba on my laptop and I can find it on my desk however when I go on excel and try to import it from my vba it does not seem to appear. If you have any idea this would help me a lot! Thank you in advance
I'm using your example for sending Api SMS from a Ms Access module. It works great!!! Thank you!!
Glad it helped!
Great Job. Very nicely explained. Thanks/
Glad you liked it
i am receiving Variable not defined in base_url, are you declaring this variable in another place?
In the video, I had `Option Explicit` on, you need to turn that off so you don't get that error. If you don't you'll need to declare each variable at the top of the code. For example, `Dim base_url As String`.
Sigma Coding thanks sir!
Thanks for the great tutorial. But i have a problem in json_parseObject at "set json _Parseobject = new Dictionary". I get "invalid use of the key new" . The Ms scripting runtime is already bound. I would appreciate any idea. I reproduced your sample in video 1.1 so i have no explanation what it could be.
Hi, had the same problem here, solved using Scripting.Dictionary instead. Hope can help
@@dolomix5688 I solved the problem by changing the priority of the library and getting this to the top.
how safe is the file you used from github? I'm trying to learn ways to make something similar to this work at my workplace but with all the licensing and stuff, I'm hesitating to possibly use it.
It should be, I haven't had any issues with it. If he gave it an Open Source License, you could use it then. I would just cite where you got it, so if other people want to see the source code they know where to go.
Thank you for great sharing. Please can you share codes for sending file to Telegram by VBA.
Hey Sigma ! Thanks for the great video. Maybe you can help me. How can i add a header on this code ? I have a token that i cant add in the url.
You just use the setRequestHeader method:
' For Content Type.
xmlhttp.setRequestHeader "Content-Type", "text/json"
' An example of Basic Auth
username = "someusername"
password = "somepassword"
xmlhttp.setRequestHeader "Authorization", "Basic " + Base64Encode(username + ":" + password)
Hello Sigma, thanks for this informative video! :)
I have some few question what if my JSON data it's kind like this:
{"results" : {
"values": [
[1,2],
[3.4]
[5,6]
]
}
I want to get all my data in values, but my values is in Array format not dictionary.
How can I get for example the One(1) value in the first Array.
Thank you.
I hope you notice this!
God bless !
You just need to index the array, that's all.
@@SigmaCoding Thanks Sigma! I actually found a work around.
But I have other problem and I already look around internet if there is work around on my problem, but I got no luck searching for answer.
I was hoping that you know the answer, or at least you already encounter this.
My API server has been shutdown, and when I request on the excel (and since server is shutdown, it doesn't give response), what happen is Excel is hang up waiting for the response.
Is there a way to set timeout on MSXML.xmlhttp60 ? it seems like there is no settings for that.
Again, thank you for responsing.
@@henroljeogutierrez1294 Take a look at this post on Stack Overflow that should do the trick. stackoverflow.com/questions/14052543/how-to-set-http-timeout-using-asp
hi there i hope you can h elp me or point me in some kind of direction .. my goal - i wanna grab data from basicly a website in my case the value of my portfolio on a crypto currency service website into excel. they provide that data over api but when i try to connect it always endsup with authorization error. of course i have the public and private key but im unable to find out so far what exactly to do with them. importing public data which doesnt require authorization works fine only this authorizastion i have no idea and for weeks im searching online not finding anything. by looking at what you are doing here i thought it might be worth trying to ask you for advice or a hint maybe you know some place where one could find a example cheet to look at how things are supposed to be done. any kind of help is much aprectiated im desprate by now and dont know what else to do than to start asking people directly for help
Hi there,
If you could, please send me the code you already have to my email coding.sigma@gmail.com and a link to the API you're using so I can read the documentation.
Please do a video on this for TD-Think or Swim with VBA! I'm able to establish a successful 200 code connection but, my parsing returns nothing! VBA will allow people to skip python>pandas>excel process!. Thanks!
Just an FYI, it'll be hard to handle all the factors related to authentication using VBA. Not to say it can't be done, but it might take a significant amount of effort. I had a client who wanted to use the TD API from VBA, and my solution to him was writing a COM Server in Python that controlled the TD API Library and then have it be ingested by VBA.
I call it a happy medium solution. You can still get VBA, but you also have the complicated parts in Python. Would you be interested in a topic like that?
@@SigmaCoding Ya that would be awesome!!! Btw any idea why TD ameritrade's API can't get real time qoutes for Forex? it seems to work fine for everything else...
@@slave2karma In my adventures of trying to access TDA API for forex it appears that the only way is to use their streaming API to access forex history prices which is probably what you want. They have sample code written in javascript for it but like Sigma said it looks like it would be a bear to emulate that code in vba. I've had some success using Excel's RTD function to access forex prices but in order for that to work for anything really useful it appears to me that you would have to create your own history DB and port price changes to records in one minute increments. I've managed to get live real data pricing but it's next to useless without a history of price changes. FWIW.