What we should build automation. Using the HTTP API in Google Sheets

In the era of mass automation, users want to "push a button and get an answer." Well, or additionally move a little mouse. Automating reports and other items that are conveniently presented in the form of a tablet is often built in Excel using its own macros or simply embedded formulas. Excel plugins won't surprise anyone now, by the way, we also have one , but this is the subject of a separate article. What about google sheets? Earlier, my colleague told how to screw our Telegram API, but I will try to tell you how to use it in Google tables.

Under the cut a bit of code and a lot of crutches.

We will work, obviously, in the browser. To write our functions, we will use Google Apps Script , which is suspiciously similar in syntax to trimmed javascript. We proceed from the principle that we do not know how to code, but we do not want to read the documentation, but we actively use the approaches outlined in the Stackoverflow Driven Development technique.

If you are a novice trader, then we suggest you read here .

Training

First we get access to the API . Free of charge (if only account managers are not tortured by calls) and without SMS, but with registration. We will not read the documentation (there are still no pictures there), and we will generate the token for access by hands via jwt.io. Why hands? Because the token generated by our site expires in an hour. This is useful, for example, for use on a website, but for normal work in Sheets, we want it to live longer, say, a year. More information about the procedure for creating a token can be found here .

Work with API

Now create an empty table and go to the script editor; if anyone does not know, you can get there by calling Tools → Script editor . In the editor, we will declare several global variables:

var BASE_URL_API = "/md/1.0"; var BASE_URL_HOST = "https://api-demo.exante.eu"; var BASE_URL = BASE_URL_HOST + BASE_URL_API; var TOKEN = "your-token-from-jwt-io"; 

Also we will set functions for working with queries:

 function _payload() { return { "method": "get", "headers": { "Authorization": "Bearer " + TOKEN } }; } function _parse(url) { var response = UrlFetchApp.fetch(url, _payload()); var code = result.getResponseCode(); if (code != 200) throw new Error(response.message); return JSON.parse(response.getContentText()); } 

More information about UrlFetchApp and its arguments can be read here . In addition, we catch codes other than 200, and show the user a “human readable” error from the request.

Static information from API

Now we will try to fasten several calls to our API . It still had to open the documentation and make sure that there are really no beautiful pictures there.

To begin with, we will write a method that implements a request for financial instruments. As I was told, to save traffic, information about the instruments was divided into two ends - /symbols/:symbolId and /symbols/:symbolId/specification :

 var SYMBOL_SPEC_FIELDS = ["leverage", "lotSize", "contractMultiplier", "priceUnit", "units"]; function EXANTESYMBOL(symbol, field) { var url = BASE_URL + "/symbols/" + encodeURIComponent(symbol); if (field in SYMBOL_SPEC_FIELDS) url += "/specification"; return _parse(url)[field]; } 

Hereinafter, the name of the financial instrument ( symbol ) must be encoded, if only because it may contain strange characters, for example, / .

Then create similar methods for working with options and futures .

 function EXANTEGROUP(group, field) { var url = BASE_URL + "/groups/" + group; return _parse(url)[field]; } function EXANTEGROUPNEAREST(group, field) { var url = BASE_URL + "/groups/" + group + "/nearest"; return _parse(url)[field]; } 

Quotes and "candles"

Candles are such a special indicator on financial charts. To understand what we are doing, it’s enough to know that one “candle” is represented by four values ​​- [___, ___, ___, ___] . We set the interval in seconds, in general, the function will look like this:

 function EXANTEOHLC(symbol, duration, what) { var url = BASE_URL + "/ohlc/" + encodeURIComponent(symbol) + "/" + duration + "?size=1"; return _parse(url)[0][what]; } 

Then a query like EXANTEOHLC("EUR/USD.E.FX", 60, "high") will return us the maximum price for the last minute.

With quotes a little more complicated. At the time of this writing, the only API for getting quotes is stream, which is inconvenient to use in Apps Script. (By the way, they promise to add a new API for a single quote in future releases). Therefore, it was necessary to nakostilyt solution of available funds. By construction, the close unclosed candle (that is, for the current minute / hour / day) is the average between the last purchase and sale prices that came, therefore:

 function EXANTEMID(symbol) { return EXANTEOHLC(symbol, 60, "close"); } 

For complete happiness, you can still make the function of converting from one currency to another:

 function EXANTECROSSRATES(from, to) { var url = BASE_URL + "/crossrates/" + from + "/" + to; return _parse(url)["rate"]; } 

Using

Now we will try to use our functions as usual methods in Excel. The first problem we face is the update of values. The fact is that Google believes that there is no need to often recalculate a user function if the parameters have not changed. In the case of quotations that are supposed to be “live”, this is a bit critical. To work around this problem, add another “mutable” (but not really), but an unused argument to our EXANTEOHLC , EXANTECROSSRATES and EXANTEMID and call it timestamp :

 function EXANTECROSSRATES(from, to, timestamp) { var url = BASE_URL + "/crossrates/" + from + "/" + to; return _parse(url)["rate"]; } function EXANTEOHLC(symbol, duration, what, timestamp) { var url = BASE_URL + "/ohlc/" + encodeURIComponent(symbol) + "/" + duration + "?size=1"; return _parse(url)[0][what]; } function EXANTEMID(symbol, timestamp) { return EXANTEOHLC(symbol, 60, "close", timestamp); } 

Now we will implement the function that will generate this timestamp .

 function EXANTEUPDATE() { SpreadsheetApp.getActiveSheet().getRange('A1').setValue(new Date().toTimeString()) SpreadsheetApp.flush(); } 

Please note that we have blatantly privatized cell A1 , and at the same time we demanded additional rights to modify the sheet. To increase security, Google recommends inserting @OnlyCurrentDoc so that the script does not ask for rights to all documents at once:

 /** * @OnlyCurrentDoc */ 

By the way, at first glance, one could simply use the NOW() function (it is one of the few that can be counted once a minute, provided there is a special tick in the Usability settings), but its value cannot be transferred to the user-defined function, sadness.

To automatically update the data once a minute, you can create a trigger for the written function in Edit → Current project's triggers :

For complete user happiness, in addition, you can add a button (done in our tablet via Insert → Drawing ... ) and link it to the EXANTEUPDATE function.

Oh, it seems that now you can work with it. Let's try to take the nearest futures contract on FORTS: Si (which is USD / RUB) and look at its candles:

But we are talking about automation, why don't we make such a tablet for 100 tools at once? Oh...

But I propose ways to work around this problem by finding the reader yourself :) Probably not the best, but quite working solution for queries of the same type, where we take only one field from JSON (for example, EXANTEOHLC ) - use the cache in global variables. A better solution is to send lists of several financial instruments separated by comma in one request (for example, for candles).

Documentation

An optional item that I missed during the story. You can arrange comments to functions in accordance with JSDoc and add an additional @customfunction , for example:

 /** * mid (average between bid and ask) value * @param {string} symbol * symbol ID * @param {string} [timestamp] * dummy parameter for update feature * @returns {number} mid value for specified symbol * @customfunction */ 

In this case, the user will see a beautiful help on how to properly use this function, what arguments it requires and what it returns. It should be noted that the Google Parsit dockstream at its discretion, but in general is very similar to JSDoc.

That's all. It seems you can now use and publish. Just cut the token :) The source code of this “script” can be found on the github under the MIT license.