Bitcoin Forum
December 12, 2017, 06:40:28 PM *
News: Latest stable version of Bitcoin Core: 0.15.1  [Torrent].
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: Absurd Request concerning API, VBA, and Excel  (Read 6879 times)
Rogue5pawn
Newbie
*
Offline Offline

Activity: 26


View Profile
October 28, 2013, 10:53:26 AM
 #1

I'm trying to have MS Excel use a VBA macro to get my account information from Cryptsy and arrange it all in a bunch of nice, pretty tables.

I know there are probably more effective ways to go about this, but I just want to know how to do it. So, if you would like to tell me about all the "better ways," I will appreciate the suggestions. Or you can just see this as a Challenge in Programming. Whatever floats your boat, I guess.

That said....

I already have a macro that loops through BTCe's Public Tickers and changes colors based on cell values. I think BTCe's Tickers update often enough for this to be somewhat informative, even if it's not fast enough to be very useful (3 seconds for full loop before restart). I use it mostly to know when I need to jump on the site when it seems more activity is happening.

I also already have a macro that pulls Cryptsy's Public SingleMarket Orders and sets up those nice tables for me..... However, the problem is that Cryptsy's Public APIs only update every ~5 minutes.... but the Private API is live.

(Why Cryptsy? Just learning the in's and out's of day trading. Seemed like as good a place as any, especially as they don't yet trade in fiat currencies.)

Here's the basic VBA (for free) that I'm looking to transform from a "GET" to a "POST"........
Code:
Sub MarketInfo()

Dim XML: Set XML = CreateObject("MSXML2.XMLHTTP")
       
XML.Open "Get", "http://pubapi.cryptsy.com/api.php?method=singlemarketdata&marketid=3", False
XML.send
       
Dim Resp As String: Resp = XML.ResponseText

Dim SellPrc As Integer: SellPrc = InStr(1, Resp, "sellorders") + 23
Dim SellQty As Integer: SellQty = InStr(SellPrc, Resp, "quantity") - 3
     Dim sPrice As String: sPrice = Mid(Resp, SellPrc, SellQty - SellPrc)
          Sheets("Sheet1").Range("C2").Value = sPrice

Dim BuyPrc As Integer: BuyPrc = InStr(1, Resp, "buyorders") + 22
Dim BuyQty As Integer: BuyQty = InStr(BuyPrc, Resp, "quantity") - 3
     Dim bPrice As String: bPrice = Mid(Resp, BuyPrc, BuyQty - BuyPrc)
          Sheets("Sheet1").Range("D2").Value = bPrice

End Sub

What I need help with is inserting the nonce, API Keys, Headers, etc. in order to access my account in the URL line. Not interested in making trades via Excel, just wanting to look at my account's stats for bookkeeping reasons. I just can't seem to Google the answers anywhere (and Bing is even worse).

Here's a link for more information about Cryptsy's API protocols: https://www.cryptsy.com/pages/api

Short question: What follows after "https://www.cryptsy.com/api"?
1513104028
Hero Member
*
Offline Offline

Posts: 1513104028

View Profile Personal Message (Offline)

Ignore
1513104028
Reply with quote  #2

1513104028
Report to moderator
1513104028
Hero Member
*
Offline Offline

Posts: 1513104028

View Profile Personal Message (Offline)

Ignore
1513104028
Reply with quote  #2

1513104028
Report to moderator
1513104028
Hero Member
*
Offline Offline

Posts: 1513104028

View Profile Personal Message (Offline)

Ignore
1513104028
Reply with quote  #2

1513104028
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1513104028
Hero Member
*
Offline Offline

Posts: 1513104028

View Profile Personal Message (Offline)

Ignore
1513104028
Reply with quote  #2

1513104028
Report to moderator
1513104028
Hero Member
*
Offline Offline

Posts: 1513104028

View Profile Personal Message (Offline)

Ignore
1513104028
Reply with quote  #2

1513104028
Report to moderator
1513104028
Hero Member
*
Offline Offline

Posts: 1513104028

View Profile Personal Message (Offline)

Ignore
1513104028
Reply with quote  #2

1513104028
Report to moderator
tactrad
Jr. Member
*
Offline Offline

Activity: 34


View Profile
February 13, 2014, 05:45:54 PM
 #2

Any progress on this?
I am stuck at the same point.

impresionesmline
Full Member
***
Offline Offline

Activity: 217


View Profile
February 17, 2014, 02:18:06 AM
 #3

Perhaps this pages could give you a hint:

A nonce example:

https://bitbucket.org/nitrous/mtgox-api/src/1dd0a12ab1d74c3bcecbb02825104e248407bd92/examples/api.vba

POST Examples:

http://social.technet.microsoft.com/Forums/scriptcenter/en-US/a2887fc4-90a6-4f27-9678-b76137f3cb22/invalid-parameters-when-using-xmlhttp60-and-post-method?forum=ITCG

http://msdn.microsoft.com/en-us/library/dd582945(v=office.11).aspx

http://social.msdn.microsoft.com/Forums/en-US/c917c50c-ef26-4184-a883-995460c002df/get-the-response-body-of-the-given-url-using-msxml2xmlhttp-in-vfp?forum=visualfoxprogeneral

http://www.bigresource.com/VB-Using-xmlHttp-correctly-ddbTZ3nEfQ.html

Take a look at this for example:
Quote
Sub XMLTest1()
Dim xmlhttp
Set xmlhttp = CreateObject("microsoft.xmlhttp")
Dim result
xmlhttp.Open "POST", "http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBmData", False
xmlhttp.setrequestheader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.send "?param1=i_isg-spow1&param5=2003-06-14&param6=*"
result = xmlhttp.responsetext
MsgBox result
End Sub

Quote
Short question: What follows after "https://www.cryptsy.com/api"?

My answer would be that the parameters such us
Quote
method=singlemarketdata&marketid=3
must be sent using xmlhttp.send method. I also think that "?" should not be included in that method.

Are you looking for a linux vps, then please take a look at this it has good prices: https://simplenode.co/hostbill/?affid=74
Pages: [1]
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!