+ Reply to Thread
Results 1 to 19 of 19

Importing website data using API

  1. #1
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Importing website data using API

    I am trying to import API data from cryptopia.co.nz

    They have publicly issued their APIs here:
    https://www.cryptopia.co.nz/Forum/Thread/255

    Let's say I choose to import this one:
    https://www.cryptopia.co.nz/api/GetCurrencies

    how should I do this?

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Importing website data using API

    Well, first, you need to get an API from the site owner and then use the API given to you on the HTTP Get method.
    I am not sure how the API on this site works, but most likely would be
    https://www.cryptopia.co.nz/(Your API goes in here)/GetCurrencies.
    You then get a JSON object. You then parse JSON object.

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Importing website data using API

    Hi,

    Something of this sort -

    Please Login or Register  to view this content.
    I am using this JSON Parser

    https://github.com/VBA-tools/VBA-JSON

    References to be added from VBE. And also Microsoft XML v6.0 (Which I forgot to add in the Screenshot)

    See the attached file
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by NeedForExcel; 01-12-2018 at 12:26 AM.
    Cheers!
    Deep Dave

  4. #4
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    I will try once I get back home from work!

    Is it possible to make it a live data? To refresh it as often as possible, for example every minute (like in PQ)?

  5. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Importing website data using API

    Quote Originally Posted by kao View Post
    I will try once I get back home from work!

    Is it possible to make it a live data? To refresh it as often as possible, for example every minute (like in PQ)?
    Yes, It is possible..

    Just let me know if the solution worked for you, I will add the refresh code once you get the code working..

  6. #6
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    Thank you very much.

    So I downloaded your excel worksheet and it looks like it works.
    The thing is, unfortunately, I do not understand a thing about VBA, so I am unable to transfer this thing to my own worksheet.

    Also, I tried to replace that url with this one:
    https://www.cryptopia.co.nz/api/GetMarkets

    then it did not work.
    Does it mean that there are some changes that must be done with each URL?

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Importing website data using API

    Hi,

    Please Login or Register  to view this content.
    If you see this part, the initial API had data for Symbol, Name, Algorithm etc.

    In this API, there is data for TradePairId, AskPrice etc.

    Please Login or Register  to view this content.
    All you have to do is change the Tags which your API has and the below line (Highlighted in Red) based on the number of tags - 1 as the Array bound starts with 0. (I have included only 5, you can include more if you want.)

    Please Login or Register  to view this content.
    Apart from the above suggested change, all you will need to change is the API URL.
    Last edited by NeedForExcel; 01-12-2018 at 06:35 AM.

  8. #8
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    Thanks.

    I change it as you said here, but it would only return me three columns, tradepairid, ask and bid prices, but no low and high prices.

    Why is that?

  9. #9
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    Quote Originally Posted by AB33 View Post
    Well, first, you need to get an API from the site owner and then use the API given to you on the HTTP Get method.
    I am not sure how the API on this site works, but most likely would be
    https://www.cryptopia.co.nz/(Your API goes in here)/GetCurrencies.
    You then get a JSON object. You then parse JSON object.
    Can I do that with XML?
    Or it has necessarily to be JSON?

    If so, how to know if my API is JSON or XML?
    I am watching this tutorial and would like to use it as an example:
    https://www.youtube.com/watch?v=mmT4qxOnEZk&t=603s

  10. #10
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Importing website data using API

    Quote Originally Posted by kao View Post
    Thanks.

    I change it as you said here, but it would only return me three columns, tradepairid, ask and bid prices, but no low and high prices.

    Why is that?
    Have you changed this line?

    ReDim Results(Parsed("Data").Count - 1, 4)

    Also, JSON or XML are output which you need to parse.

    Both have different structures and can be figured out if it is XML/JSON just by looking at the output.

    You can have your API return XML as well as JSON, both can be parsed.

  11. #11
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    Yes, I did.
    I am using exactly this line now:
    Please Login or Register  to view this content.
    So, when working with the API in my case, I can do both - XML and JSON? Its of my choice?

  12. #12
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Importing website data using API

    Quote Originally Posted by kao View Post
    Yes, I did.
    I am using exactly this line now:
    Please Login or Register  to view this content.
    So, when working with the API in my case, I can do both - XML and JSON? Its of my choice?
    Yes,

    But the Codes will change..

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Importing website data using API

    You do not have any control. It is the owner's decision to use XML or JSON or both. Both XML and JSON are data exchange formats.
    Quote Originally Posted by kao View Post
    Can I do that with XML?
    Or it has necessarily to be JSON?

    If so, how to know if my API is JSON or XML?
    I am watching this tutorial and would like to use it as an example:
    https://www.youtube.com/watch?v=mmT4qxOnEZk&t=603s
    Last edited by AB33; 01-16-2018 at 06:44 AM.

  14. #14
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Importing website data using API

    Quote Originally Posted by kao View Post
    Yes, I did.
    I am using exactly this line now:
    Please Login or Register  to view this content.
    So, when working with the API in my case, I can do both - XML and JSON? Its of my choice?
    This line is changed a bit

    Please Login or Register  to view this content.
    Notice + 1 added at the end. I missed it out by error.

    This should do it.

  15. #15
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    Quote Originally Posted by AB33 View Post
    You do not have any control. It is the owner's decision to use XML or JSON or both. Both XML and JSON are data exchange formats.
    So when I open the API, how to find out if it's XML or JSON?

  16. #16
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Importing website data using API

    The easiest way is to find it in the API's documentation. You can also find it on the header response.

  17. #17
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    Hello,

    I have been unsuccessfully trying to import this API into excel:

    https://api.kraken.com/0/public/Ticker

    Could anybody help me with this please?

  18. #18
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    still looking for help!!

  19. #19
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Importing website data using API

    How to make this macro refresh itself automatically as often as possible without bringing me to the sheet the macro belongs?

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Importing data from website
    By unalak in forum Excel General
    Replies: 2
    Last Post: 11-30-2016, 11:22 AM
  2. Importing data from a website using vba
    By amber_skanpur in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-04-2014, 10:18 AM
  3. importing external data from a website
    By ppg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-18-2009, 09:46 AM
  4. Automatically importing data from a website
    By amawad98 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2008, 01:18 PM
  5. Automatically importing data from a website
    By jgn2112 in forum Excel General
    Replies: 2
    Last Post: 07-21-2008, 11:40 AM
  6. Importing website data into excel
    By astrikor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2008, 08:29 PM
  7. [SOLVED] Importing data from a website??
    By Galactik in forum Excel General
    Replies: 1
    Last Post: 01-29-2005, 11:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1