bookmark_borderGoogle Sheets API – batchUpdate

batchUpdate() works on values if you use sheet.values().batchUpdate() and works on sheet properties if you use sheet..batchUpdate()

So I wanted to be notified for my tradingBot (made in Python), if a certain KPI was met. On my Mac, no problem. I use Notify.

from notifypy import Notify

But I wanted to receive a notification even when I am not sitting at my computer. I figured with the help of Google I could achieve this. So I write to a Google Sheet some values and there I trigger an AppScript which sends me an email if KPI is met.

First attempt:

    values = [[datetime, last, change, gain, loss, rsi]]
    part1 = {
        'values': values

    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A2:F2",
        valueInputOption="RAW", body=part1).execute()

    values = [["insert into ticker (datetime, last, change,gain,loss) values ( '{}',{:f} ,{:f} ,{:f}, {:f} )".format(
        datetime, last, change, gain, loss, rsi)]]
    part2 = {
        "values": values
    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A5",
        valueInputOption="RAW", body=part2).execute()

execute() was called twice, for each row I update. Hence triggering the AppScript (which sends an email) twice. I always wondered why I receive duplicates. Couldn’t figure it out.

Then I received a warning from Google: too many emails sent, with some sort of Log

Google AppScript Log

So I saw: aha, it actually calls the “myFunction” twice. I immediately knew it had to do with how I update the sheet.

I quickly figured there’s a “batchUpdate” function. But documentation was awfully bad. Sheets does know 2 different kind of requests. One kind updates the sheet’s properties (colours/rows, design stuff). The other updates it’s values. BUT they don’t tell that very explicitly. By luck I figured it works like this:

    values = [datetime, last, change, gain, loss, rsi]
    part1 = {
        "range": "Sheet1!A2:F2",

        "values": [

    values = [["insert into ticker (datetime, last, change,gain,loss) values ( '{}',{:f} ,{:f} ,{:f}, {:f} )".format(
        datetime, last, change, gain, loss, rsi)]]
    part2 = {
        "range": "Sheet1!A5",
        "values": values

    requests = {
        "valueInputOption": "RAW",
        "data": [

            part1, part2



and the important part is the “.values()” part in the last line. I first tried without it and Google told me: don’t know range, don’t know data, don’t find fields and other stuff.

As soon as I added the .values() it worked seamless

Looking forward to your comments

bookmark_borderOther stuff

Improving blogger experience

Almost forgot to mention how I improved blogger experience.
I needed a TOC (Table of contents), such that a reader can quickly jump to the section he's interested in.
Blogger does NOT offer this out of the gate.
So I dug, dug deeper and I found 2 solutions which actually create a TOC. But neither was good enough. 
One only fetched only level of <H> tags, the other one fetched all but got lost as soon as there's more complicated HTML code between the <H> tags.
So I used some parts of both and improved upon it: You paste this in the HTML BEFORE everything:
<div id="myToc">
<hr />
<div id="myContents">
And you paste this AFTER everything
<script src=""></script>
<script type="text/javascript">
countChapters = $("#myContents > h1, #myContents > h2").length
chapters = $("#myContents > h1, #myContents > h2")
flagLevel = false
var toc = ''
for (i = 0; i < countChapters; i++) {
chapter = chapters[i]
chapterTitle = chapters[i].textContent;


chapters[i].setAttribute("id", "chapter" + i);
if ('H2' == chapter.tagName) {
if (flagLevel) {
toc += "<li><a href='#chapter" + i + "'>" + chapterTitle + "</a></li>";
} else {
toc += "<ul><li><a href='#chapter" + i + "'>" + chapterTitle + "</a></li>";
flagLevel = true
} else {
if (flagLevel) {
toc += "</ul>"
flagLevel = false
toc += "<li><a href='#chapter" + i + "'>" + chapterTitle + "</a></li>";
document.getElementById("myToc").innerHTML = toc;
Then it will parse all H1/H2 tags and create a TOC at the top.
By fine-tuning the flagLevel thing you could even have it parse H3 tags.
Who's interested: let me know and I enhance the code for you!
This technique can actually be used on every HTML page

Install web server

This is an easy one on macOS, thanks to this
I had home-brew (brew) already installed. So I just needed to follow a few steps.
Apple delivers a built-in apache version, yet the default configuration is a bit not so straight forward. With the approach from above I had the web server running in a few minutes, pointing to my directories.

Make (bot) results accessible from afar

Here I needed to think/play a little bit. Years ago there were DynDNS services, for free. Nowadays they charge for their service. What they basically do is provide a hostname which is mapped to your dynamic public IP from your home router.
So I tried to build my own "rough" DynDNS solution.
I started with and a tool which lets you parse HTML on the server (and not on the client side). Here's a nice tutorial
On the way I learned that jQuery is a thing of the past and one should use React or Vue now, so I did a 30 minutes primer into Vue. Nice, but couldn't understand why/where it's better than jQuery. With Vue you have to "wrap" your tags into "v-" tags in order for it to work. Works well for applications, but it's a pain if you want to do very small things, like things I did with my visual tool to analyse charts.
Anyway: I got cheerio to read the contents of and it took me almost 2 hours to figure, that they would not return my IP, as they realise the request is not from a user, but from a machine.
But there's other tools, like this one: Very straight forward. Returns my IP and that's all I need.
Now what to do with my IP? Store it on google drive? Do they have an API? After a few minutes tinkering I figured: I will use iCloud. Create a document there with a link to my "own" web server. And that's what I did.
Please don't judge me on this code: it's a prototype and does what it needs to do:
const axios = require("axios").default;


url = ''


const fetchHtml = async url => {
try {
const { data } = await axios.get(url);
return data;
} catch {
`ERROR: An error occurred while trying to fetch the URL: ${url}`


async function doIt() {


var result = await fetchHtml('')
var path = '/Users/michael/Library/Mobile Documents/com~apple~CloudDocs/HTML/'
var ip = result.query
var fs = require('fs');
fs.writeFileSync(path + 'myServer.html', `</br></br></br></br></br><H1><a href="http://${ip}/heartBeats">myServer</a></H1>`)
fs.writeFileSync(path + 'myTool.html', `</br></br></br></br></br><H1><a href="http://${ip}:3000">myTool</a></H1>`)


Important here is the first line. This is required as cron, which will run the script, does not know where my node modules are installed
If fetches the IP address from my router (via this URL), stores it in a link in a HTML file in a folder in iCloud where I can access it from anywhere in the world.
I only needed to to some port forwarding in my router. Port 80/3000 need to forward to my machine.
Interestingly, once I set it up, he, the router software, told me: my Mac will from now on have a static address. How nice!
Final piece: I created a a soft link in my web documents folder to the folder where I store my bot heart beats. So now I can call my server like:
and it will show me the directory listing of my heartBeats so I can check from everywhere if my bots are running.


bookmark_borderCreate a tool to visually analyse data


There are so many prices, the oscillation is gargantic.

I want to be able to detect areas where my bot could have succeeded, such that I can analyse patterns which would let my bot make the right decision for a trade.

So I first need to “detect” such areas, and then I need to find out “where” in my data points those areas are.


First I tried Numbers, an Excel-like spreadsheet application which comes with every Mac. While for a small amount of numbers I created the perfect solution, for large amount of numbers to analyse the process slows down, drastically.

So I had to rethink. Why not use the data from the database directly?

That basically meant I need a tool to display the chart from data from the database, and I needed a simple responsive web server.

There’s lots of such tools. I decided to go with Google tools, check out the Guides and the References part.

I did find the perfect tool, yet it was very poorly documented and thus I had to start low. Remember “De- complexify problems”

  • do everything manually, use manual (hard coded data)
var data = new google.visualization.arrayToDataTable([
[‘Opening Move’, ‘Percentage’],
[“King’s pawn (e4)”, 44],
[“Queen’s pawn (d4)”, 31],
[“Knight to King 3 (Nf3)”, 12],
[“Queen’s bishop pawn (c4)”, 10],
[‘Other’, 3]

notice the “true” parameter, it says: no column headers

data = new google.visualization.arrayToDataTable(dataFromDB, true)

now I had data, but no column titles

so I changed my result from db to include column headers

rows.unshift({ “price”: “price”, “id”: “id” })

but then I had to change that parameter to false

data = new google.visualization.arrayToDataTable(dataFromDB, false)
  • now the table was too long and didn’t scroll

I figured after long looking and testing, there’s options you can pass to the view

var optionsTable = {
width: 265,
chartArea: { ‘width’: ‘100%’, ‘height’: ‘65%’ },
page: ‘enabled’,
pageSize: 40,
allowHtml: true,

the “page” parameter does it, the result looks like this

2 things were missing:

I need to be able to page data, like fetch a 1000, then fetch another thousand

I wanted to “interact” with the chart

So for the “paging” mechanism I built to buttons which would fetch the “next” or the “prev” page of data, page being a defined number of rows

the two buttons

<button onclick=javascript:getPage(‘prev’)>Load Previous Page</button>
<button onclick=javascript:getPage(‘next’)>Load Next Page</button>

the code being called

function getPage(direction) {
var scriptUrl = “/data?direction=” + direction;
url: scriptUrl,
type: ‘get’,
dataType: ‘html’,
async: false,
success: function (data) {
myJson = JSON.parse(data)
let myArray = => Object.values(obj));
dataFromDB = myArray;

the code on the server being executed, see the “next” / “prev” actions, how this influences the paging (page++ or page–) from the db, see the limit clause

else if (action == undefined) {
sql = `SELECT price ,id from ${table} limit ${page * 500}, 1000`;
db.all(sql, (err, rows) => {
if (err) {
rows.unshift({ “price”: “price”, “id”: “id” })
} else if (action == “next”) {
sql = `SELECT price, id from ${table} limit ${page * 500}, 1000`;
db.all(sql, (err, rows) => {
if (err) {
rows.unshift({ “price”: “price”, “id”: “id” })
} else if (action == “prev”) {
sql = `SELECT price, id from ${table} limit ${page * 500}, 1000`;
db.all(sql, (err, rows) => {
if (err) {
rows.unshift({ “price”: “price”, “id”: “id” })

But how about “interaction” with the chart? Turns out, you can select points by default. But I wanted more, guess what, there’s options = {} too you can supply to the chart view. You can supply options to zoom in, to reset, for tick marks on the scale and you can turn on multiple selections.

var options = {
width: 1000, height: 800,
chartArea: { ‘width’: ‘80%’, ‘height’: ‘80%’ },
selectionMode: ‘multiple’,
explorer: { actions: [‘dragToZoom’], maxZoomIn: .01, keepInBounds: true },

So far so good, but I wanted to do something with that info. As a first step, I intercepted the clicked points, made sure only 2 can be selected, and I changed the tooltip, which per default was info from the table. Now I wanted the tooltip to show percentage change from the first point clicked.

After a lot of testing I figured it out., ‘select’, selectHandler);
// make sure we only have 2 points selected
var selectedPoints = []
function selectHandler(e) {
selectedPoints = chart.getSelection()
var selectedItem = chart.getSelection()[selectedPoints.length – 1];
if (selectedItem) {
// make sure we only have 2 points selected, delete one if we select 3
if (2 < selectedPoints.length) {
// if 1 point is selected, update the tool tips of all others with percentage change related to the first selected point
if (1 == selectedPoints.length) {
for (i = 0; i < dataFromDB.length – 1; i++) {
point1 = data.getValue(selectedPoints[0].row, selectedPoints[0].column)
point2 = data.getValue(i, 1)
change = (point2 / point1 * 100) – 100
if (0 > change) {
data.setCell(i, 2, `<p style=”font-size:25px; text-align: right”>price: <b>${point2}</b><br><font style=”color:red”>${change.toFixed(2)}%</font></p>`)
} else {
data.setCell(i, 2, `<p style=”font-size:25px; text-align: right”>price: <b>${point2}</b><br>${change.toFixed(2)}%</p>`)
positive change in price

positive change in price

Turns out, that for HTML tooltips I needed to enable in 2 areas the HTML option. I figured this out by accident. You can see this in the code above which shows the options for the chart and here:

data.addColumn({ ‘type’: ‘string’, ‘role’: ‘tooltip’, ‘p’: { ‘html’: true } });

And this is the full prototype

and here it is in action

Intercept key presses


I want to be able to mark data points in my database for which I should need to do some deeper analysis. I want to do this via key press (eg <ctrl><s>).
I also want to be able to simpler remove already selected points from the line chart. The “normal” way is clicking them again to unselect them. But this is rather daunting cause you need to be accurate.


Don’t even bother handling key events yourself. Let jQuery do that for you. It does it nicely, and (hopefully) across all browsers.
$(document).keydown(function (event) {
// <ctrl><s> shall mark the point in the db
if (event.ctrlKey && event.which == 83) {

Adjusting my line chart

So while I am developing my bot I try to improve its “clairvoyance”, such that he may predict a drop or rise better. So I am introducing additional parameters I want to display in my line chart.
Doesn’t help a lot if the red line is squeezed to the bottom due to having totally different values than the blue line.
After hours of searching I figured these settings in the display options of the chart help.
series: {
0: { targetAxisIndex: 0 },
1: { targetAxisIndex: 1 }
}, vAxes: {
0: {
minValue: 20, maxValue: 70
}, 1: {
minValue: 0.2, maxValue: 0.7
series: {
0: { targetAxisIndex: 1 },
1: { targetAxisIndex: 0 }
}, vAxes: {
1: {
minValue: 20, maxValue: 70
}, 0: {
minValue: 0.2, maxValue: 0.7

How to zoom-out of a chart

Zooming in in Google charts is a breeze. You just need to set the appropriate option, see chapter “Create a tool to visually analyse data”.
But if you want to zoom out step by step it’s a bit more complicated. But I found some code from the same guy ( from whom I “stole” already the code to “reset” the zoom level in case I need a redraw, which I do if I click a point and I need to change all tooltips.
So the new piece of code is this here:
var container = document.getElementById(‘chart_sort_div’), ‘ready’, function () {
zoomLast = getCoords();
var observer = new MutationObserver(function () {
zoomCurrent = getCoords();
if (JSON.stringify(zoomLast) !== JSON.stringify(zoomCurrent)) {
zoomLast = getCoords();
console.log(‘zoom event’);
observer.observe(container, {
childList: true,
subtree: true
chart.draw(viewChart, options);
Pay attention to the “container” variable!
And this is also needed for the right click not to reset, but to zoom out:
function zoomOut() {
options.hAxis.viewWindow = {};
options.vAxis.viewWindow = {};
coords = zoomCoords.pop()
if (coords) {
options.hAxis.viewWindow.min = coords.x.min;
options.hAxis.viewWindow.max = coords.x.max;
options.vAxis.viewWindow.min = coords.y.min;
options.vAxis.viewWindow.max = coords.y.max;
chart.draw(viewChart, options);
and a global variable
var zoomCoords = []
and that’s it! Again, so simple 

Adding a slider to move between pages of data

I first had added a prev/next page button. But since then my data has grown and I need to be able to move faster than click 100 times the next button.
How about using a “slider” like this
Fortunately jQuery UI has a slider tool as standard. The normal slider doesn’t show the value, so how do you know where you are? Easy! There’s a “slide” event on the slider, use it to read the current “value” and set that value as text of the slider element:
<!doctype html>
<html lang=“en”>
<meta charset=“utf-8”>
<title>slider demo</title>
<link rel=“stylesheet” href=“//”>
#slider {
margin: 10px;
<script src=“//”></script>
<script src=“//”>
<div id=“content”>
<div id=“slider”></div>
$(“#slider”).slider(“option”, “max”, 50);
$(“#slider”).slider(“option”, “min”, 10);
sliderHandle = “.ui-slider-handle” //insideHTML
$(“#slider”).on(“slide”, function (event, ui) {
Now obviously you need to know which tables have how many pages to display. Add something like this to your server:
app.get(‘/pages’, (request, response) => {
sql = `SELECT count(id) as count from ${table}`;
db.all(sql, (err, rows) => {
if (err) {
Make sure you call the “/pages” on load somewhere on the HTML document
var scriptUrl = “/pages” + name;
url: scriptUrl,
type: ‘get’,
dataType: ‘html’,
async: false,
success: function (data) {
count = JSON.parse(data)[0].count
pages = Math.ceil(count / 500)
On slide stop you want to retrieve the actual data page of your db:
$(“#slider”).on(“slidestop”, function (event, ui) {
value = $(“#slider”).slider(“option”, “value”)
var scriptUrl = “/data?page=” + value;
url: scriptUrl,
type: ‘get’,
dataType: ‘html’,
async: false,
success: function (data) {
myJson = JSON.parse(data)
let myArray = => Object.values(obj));
dataFromDB = myArray;
There’s lots of opportunities to customise further


Creating a responsive web server

For the visual tool to analyse my charts I needed something to deliver the data to the tool.
While I was a few months back playing around with ideas and technologies, I came across NodeJS. Which is the easiest way for you to create a Webserver based on JavaScript language.
Don’t believe me? Checkout the document Introduction into programming from the ZIP file you find in the description of this video:
There you find information to get started with NodeJS.
Let me tell you: it is so easy, it makes me totally happy to be able to return data for asynchronous calls to my webpage within a few minutes.
var path = require(‘path’)
var express = require(‘express’);
var app = express();
var session = require(‘express-session’)
secret: ‘chosen language’,
saveUninitialized: true,
resave: false,
var server = app.listen(3000, function (err) {
app.use(express.static(__dirname + ‘/’));
if (err) {
console.log(“Server listening at port 3000”)
Obviously it does not do much besides listening to incoming requests on port 3000.
You add a little bit of code like:
app.get(‘/’, (request, response) => {
response.sendFile(path.join(__dirname, ‘index.html’));
app.get(‘/manual’, (request, response) => {
response.send(“Hello world, no HTML code, pure text”)
app.get(‘/test’, (request, response) => {
let path = url.parse(request.url).pathname;
json = { Name: ‘Michael’, LastName: ‘Zischeck’ }
Then you can call your web server like:
http://localhost:3000/ (this will serve you the index.html from the same directory as from where you run the server
Isn’t that simple?
How about returning data from a database, sqlite3 in this example:
app.get(‘/data’, (request, response) => {
let path = url.parse(request.url).pathname;
var parts = url.parse(request.url, true);
var query = parts.query;
action =
console.log(“action:” + action)
if (action == undefined) {
sql = ‘SELECT id, price from tableName limit 0, 1000’;
db.all(sql, (err, rows) => {
if (err) {
rows.unshift({ “id”: “id”, “price”: “price” })
} else if (action == “next”) {
sql = `SELECT id, price from tableName limit ${page * 500}, 1000`;
db.all(sql, (err, rows) => {
if (err) {
rows.unshift({ “id”: “id”, “price”: “price” })
} else if (action == “prev”) {
sql = `SELECT id, price from tableName limit ${page * 500}, 1000`;
db.all(sql, (err, rows) => {
if (err) {
rows.unshift({ “id”: “id”, “price”: “price” })
That’s it! Complete code for my visual chart analysis tool! How easy is this?

Going back to Python

While I was almost done with my trading bot in NodeJS, I basically converted the most important parts of my Orst attempt in Python, it happened that when I run simulations, like tens of thousands of rows to process, NodeJS went nuts.
I really tried, and I really would have wanted to stay with NodeJS, but I won’t! To much asynchronous.
In Python I have everything under control.
What I will leave in NodeJS is my other project. A tool to analyse XRPL accounts.
The good news though is this:
As I ported from Python to NodeJS, I simplified a lot. Made the whole logic more straightforward, and guess what: I found a simple, very stupid but very powerful bug in my logic!
So even if this sideway to NodeJS and back to Python was just about to figure out this stupid bug, then it was worth it.
Actually it was not even a bug, it was more a strange thing in my logic.
It boils down to this:
With my trading bot I want to prove that even in bearish markets I can profit. So basically I apply the Stop Loss strategy explained here.
In short:
If prices drop, I sell, and I rebuy at lower price, if possible.
Sometimes, the price does not drop further after I sold, but keeps rising. Then I have to rebuy in at a higher price, and at a loss obviously.
That’s all part of the “strategy”, the idea being I can buy more often low, than I have to buy high. And that I can buy relatively lower than I have to buy higher.
But there’s times, hopefully a lot of times, where the prices just keep rising. What then? Well I have to adjust my triggers as to when I want to sell. Those triggers keep rising.
In my old code I would “UP THE ANTE” every time a new high was reached. With the stupid effect, that, obviously, after the high the price starts dropping any my bot does its duly work: it sells (unfortunately at a way to high price).
I figured now that I should better only “UP THE ANTE” a little bit, like a 1/10 of the previous high price and the new high price.
Like old price (for my calculations) was 1$, new price is 1.20$. Now I will only increase the price for my calculations to 1.02$ and not to 1.20.
With the effect, that I “grow” into rising prices without ever risking selling too early again.
So today I finished my bot, it will start in 15 minutes to run, for a whole day long. Then we’ll see what happens.

Bot simulation

For my bot simulations I wanted to calculate as many possibilities as possible.
I have 3 triggers: sell low, rebuy lower, or rebuy higher (in case the price does not drop to the rebuy lower level). I wanted to see the outcome for all possibilities, in order for me to chose the right levels for the 3 triggers.
Let me give you an example to understand this:
Price of the asset is 10
It drops, sell trigger defines when to sell.
Is sell trigger at 10% then I would sell at 9.
If rebuy low trigger is another 10% lower, I would rebuy at 8 (for simplicity).
If however I am unlucky and the price starts rising after I sold in expectance the price would drop lower, then I surely don’t wanna miss out on the price increase either. So I have to, sour it is, bit the lemon, and rebuy at a higher level, say 10% too.
That means I sold at 9 and I will buy again if it drops to 8 (which is good) or rises to 11 (which is bad).
The same game I could make with 1%.
Meaning I sell at 9.9, rebuy at either 9.8 or 10.1.
You get the idea.
Now all those percentages, when the price changes by how much I sell, rebuy low or rebuy high:
there’s MANY possibilities.
And I want to make sure my bot uses one which proposes good enough profits.
I have now, started afresh 17th January, around 120k data points from Bitstamp, and around 400k data points from Binance.
So one simulation (eg. sell: -1%, low: -2%, high: 3%) will need to be calculated with 120k prices. Assumption, of course, is, that price development is always similar.
So what did I do?
I created my bot from the beginning, to be able to make simulation runs. I can feed as many simulations at once as I want. I have 3 levels, sell/low/high, I want to adjust.
My initial code looked like this for running this deep analysis was:
s = –0.3
down = –0.1
up = 0.1
count = 0
while s >= –4:
l = –2
while l >= –5:
h = –1
while h <= 5:
count += 1
h += up
print(“s:{}tl:{}th:{}”.format(s, l, h))
l += down
s += down
and the result something like this
s:-0.3 l:-2                 h:5.099999999999998
s:-0.3 l:-2.1         h:5.099999999999998
s:-0.3 l:-2.2         h:5.099999999999998
s:-0.3 l:-2.3000000000000003 h:5.099999999999998
s:-0.3 l:-2.4000000000000004 h:5.099999999999998
s:-0.3 l:-2.5000000000000004 h:5.099999999999998
s:-0.3 l:-2.6000000000000005 h:5.099999999999998
s:-0.3 l:-2.7000000000000006 h:5.099999999999998
s:-0.3 l:-2.8000000000000007 h:5.099999999999998
s:-0.3 l:-2.900000000000001 h:5.099999999999998
s:-0.3 l:-3.000000000000001 h:5.099999999999998
s:-0.3 l:-3.100000000000001 h:5.099999999999998
s:-0.3 l:-3.200000000000001 h:5.099999999999998
s:-0.3 l:-3.300000000000001 h:5.099999999999998
s:-0.3 l:-3.4000000000000012 h:5.099999999999998
s:-0.3 l:-3.5000000000000013 h:5.099999999999998
s:-0.3 l:-3.6000000000000014 h:5.099999999999998
s:-0.3 l:-3.7000000000000015 h:5.099999999999998
s:-0.3 l:-3.8000000000000016 h:5.099999999999998
s:-0.3 l:-3.9000000000000017 h:5.099999999999998
s:-0.3 l:-4.000000000000002 h:5.099999999999998
s:-0.3 l:-4.100000000000001 h:5.099999999999998
s:-0.3 l:-4.200000000000001 h:5.099999999999998
s:-0.3 l:-4.300000000000001 h:5.099999999999998
my revised code is now.
s = –3
down = –1
up = 1
count = 0
while s >= –40:
l = –2
while l >= –50:
h = –1
while h <= 50:
count += 1
h += up
print(“s:{}tl:{}th:{}”.format(s, l, h))
l += down
s += down
and my revised result:
s:-3     l:-21 h:51
s:-3     l:-22 h:51
s:-3     l:-23 h:51
s:-3     l:-24 h:51
s:-3     l:-25 h:51
s:-3     l:-26 h:51
s:-3     l:-27 h:51
s:-3     l:-28 h:51
s:-3     l:-29 h:51
s:-3     l:-30 h:51
s:-3     l:-31 h:51
s:-3     l:-32 h:51
s:-3     l:-33 h:51
s:-3     l:-34 h:51
s:-3     l:-35 h:51
s:-3     l:-36 h:51
s:-3     l:-37 h:51
s:-3     l:-38 h:51
s:-3     l:-39 h:51
s:-3     l:-40 h:51
s:-3     l:-41 h:51
s:-3     l:-42 h:51
s:-3     l:-43 h:51
So now I have values I can just divide by 10 and they are as accurate as accurate can be with floating points.
Now this piece just made sure that the loops would create all the possibilities I wanted to calculate.
Obviously my bot logic and the log part (file or db) is missing.
That’s what I mean by decomplify your problems. Make one thing understood clearly, then add the next complexity level.

Profiling code

As it turns out for >100k price points my simulation run 2 hours. While, for 7 million decisions to take, quite acceptable, I wanted to improve on that.

Comes in cProfile
Add this to your code:
import cProfile‘main()’)
and it will give you a result like this:

         613716 function calls in 0.530 seconds

   Ordered by: standard name

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)

        1    0.000    0.000    0.529    0.529 <string>:1(<module>)

        1    0.000    0.000    0.000    0.000

     9999    0.260    0.000    0.523    0.000

   127159    0.037    0.000    0.037    0.000

   391749    0.185    0.000    0.185    0.000

     7318    0.011    0.000    0.012    0.000

     9999    0.003    0.000    0.003    0.000

     9999    0.018    0.000    0.024    0.000

        1    0.000    0.000    0.000    0.000

    10027    0.002    0.000    0.002    0.000

        1    0.000    0.000    0.000    0.000

     9999    0.001    0.000    0.001    0.000

        1    0.000    0.000    0.000    0.000

     7370    0.001    0.000    0.001    0.000

        1    0.006    0.006    0.529    0.529

        1    0.000    0.000    0.530    0.530 {built-in method builtins.exec}

     9999    0.001    0.000    0.001    0.000 {built-in method builtins.len}

        3    0.000    0.000    0.000    0.000 {built-in method builtins.print}

        2    0.000    0.000    0.000    0.000 {built-in method now}

    10051    0.002    0.000    0.002    0.000 {method ‘append’ of ‘list’ objects}

        1    0.000    0.000    0.000    0.000 {method ‘commit’ of ‘sqlite3.Connection’ objects}

        1    0.000    0.000    0.000    0.000 {method ‘disable’ of ‘_lsprof.Profiler’ objects}

       53    0.000    0.000    0.000    0.000 {method ‘format’ of ‘str’ objects}

     9979    0.003    0.000    0.003    0.000 {method ‘pop’ of ‘list’ objects}

        1    0.000    0.000    0.000    0.000 {method ‘strftime’ of ‘’ objects}

Run this over a decent amount of data points, not all, Ogure out where you can improve your data, improve it, test again, see the difference and stick with the change or revert back.
I could improve with 2 little changes almost 25% performance, which is 30 minutes on a 2 hour run.
But over time, seeing which ranges totally never make sense, I can fine-tune, eliminating some trigger bands for sell/low/high and thus increase simulation time even further.

Sending messages to running code

Up till now I didn’t know it’s possible to send a “message” to an already running piece of code. Okay I knew about event handlers, but not that “sending signals” (or messages) is possible.
So I figured my bot is doing normally quite well, but in times of very high volatility, or very steep price increases, he gets lost and I want to stop trading during these moments.
So far I’d have to kill the process, change the script which runs the process every hour, to not run the process. And if I wanted to start trading again I would undo those changes.
Now with signals it’s quite easy.
I send a signal, I use USR1/USR2 (for stop/start trading) and intercept and change a flag in my logic.
I have a flag: executingTrades. I simply switch this flag and my bot stops or restarts trading.
How to send a signal?
Use the kill command in a shell script:


# find the pid from the particular bot I want to stop trading

pid=`pgrep -f “[C]ellar.*”`

# send signal usr1

kill -s usr1 $pid

echo “ trading stopped”


In the python code for the websocket you simply add these lines before going into .run_forever


signal.signal(signal.SIGINT, receiveSignal)
signal.signal(signal.SIGUSR1, receiveSignal)
signal.signal(signal.SIGUSR2, receiveSignal)
and the appropriate function which handles the signals
def receiveSignal(signalNumber, frame):
global logic
if signalNumber == signal.SIGINT:
elif signalNumber == signal.SIGUSR1:
logic.simulate = False
elif signalNumber == signal.SIGUSR2:
logic.simulate = True
except Exception as e:
Tried and tested on a bot which just consumes but does not act on the messages coming from the websocket.

bookmark_borderSchedule programs on macOS


While I could use the WebSocket application as a kind of “heartbeat”, there may arise situations where this is not the optimal solution.
For this purpose I run on my machine an application which fetches the latest trades, calculates average price (not average weight price yet) and stores it in a db.
But I need to run that piece of code every minute exactly.


Under macOS (Big Sur or any other) there’s two ways to define a scheduled application. Pre Big Sur I used the Launch Agents functionality, which requires a plst file with relevant attributes/data. This file is then kind of “loaded” into the launchctl daemon.
That was the first approach and it failed miserably trying to load the plst file.
So I googled a bit and found this solution:
It pretty simple explains how you can use crontab to do the same. And it actually works.
So, remember “De-complexify problems”, I first did a very simple test. A little Python application which would just print the date. I didn’t even know yet where it would print it (in the system console maybe?)
import time
from datetime import datetime
timestamp = time.time()
So I added a first scheduled script to crontab:

* * * * * /Users/michael/Python/>>/Users/Michael/Python/output.log

So I expected to see a an output.log file but I didn’t.

Fortunately, this was new to me, cron sent me a mail on the machine.

There it said: Operation not permitted.

Here the article (see link above) helped. I needed to add cron to the Applications which have Full Disk Access.

Now it worked like a charm.

Next step was to try and have cron run my python script which would fetch every minute data from Bitstamp.

This failed miserably. And it took me hours to figure out what the reason was.

The error message, as before I received emails with the error, said:

ImportError: No module named requests

When I ran the same script manually it worked like a charm. So it looked like there was a problem with the installation of the module requests, at least cron didn’t seem to be able to find that module.

I tried installing in another directory, failed.

I tried uninstalling, reinstalling, it installed in the same directory, failed.

Then I found help via Google search. You can actually prepend the PYTHONPATH to the cron tab command, like this:

* * * * * PYTHONPATH=/usr/local/lib/python3.9/site-package /Users/michael/Python/

Ever since the script runs ever minute and does it’s job: retrieve the trades from the last minute from Bitstamp.

Make sure output is written immediately to log files

As I have several cron jobs running, outputting some data, specially to see they are live and running, I had a problem: those jobs would only print to my out Oles after they had received a certain amount of data. This resulted in a not so chronological output. I had to make sure that the output is sent immediately.
And, alas, there’s a solution: unbuffer!
Install unbuffer with brew
brew install expect
Then issue your calls like:
/usr/local/bin/unbuffer python3 >> pathToYourLog.txt
The paths should be ABSOLUTE for them to work 100%. and it works, output is immediately written.
Another thing I learned and tested already: One can call simply scripts from crontab. This makes crontab way more easy to read and I can put the heavy lines into a simple shell file.

Simplify scheduled jobs

As mentioned above I use crontab on macOS cause on Big Sur somehow the launchd doesn’t load my plist files properly. Others report the same issue.
On crontab it’s crowded. One line can be more than 200 characters long, which makes editing (and even worse viewing) a pain. Reason for that long line is:
I need to add the proper PYTHONPATH, the unbuffer from the last chapter, the proper path to python3, the proper path to my script and finally the proper path to my out file.
And using those shell editors ain’t a lot of fun either.
But with BBEdit on my Mac, my dearest most loved and used companion since I have Macs, editing files is a breeze. So the route is: creating a shell script which does what the cronjob would do, and call from the cronjob the script. Sounds a bit complicated, having a 2 step process now as compared to one step before.
cronjob => python script
cronjob => shell script => python script
The advantage is a much easier interface to maintain my scheduled jobs, cause editing the shell script is way easier than editing the crontab, and crontab is very simplistic now, with only about 90 characters per job.
I had a problem though now: In case I need to “interrupt” my scheduled jobs, how would I achieve this? Turns out I can send any “signal” to my python script. Before I always used KILL only. But this prevented the script from doing clean up work, such as deleting the lock file. By using another signal, INT in this case, I can intercept and react accordingly.
List of available signals:


INT seems the same as <ctrl><c> which I use if I manually invoke those scripts.
Unfortunately this didn’t seem to work, at first at least.
Python code to act upon:
import signal
signal.signal(signal.SIGINT, receiveSignal)
Use the receiveSignal handler to handle what needs being handled, ie. getting rid of lck files.
Kill won’t let the code react on it, it simply terminates
If you run a python script by cron, will spawn always 2 processes.
And I didn’t know about that. Well I knew I always had 2 processes, but didn’t know why. Killing one killed the other usually as well.
Now knowing one is the script and the other one is python runtime (can we call it like that?) I just need to send the signal to the proper process.
And I’m doing this with a nice little shell script which ps|grep and finds the process id to kill the appropriate process.


process=$(ps -ax|grep -v grep|grep “Cellar.*”)


kill -s int $pid

echo “ terminated”

End result:
scripts which send their output immediately to the out file and shell scripts which let me easily terminate those processes without to much of manual work.

Delete lock files upon machine boot/reboot

Giving launchd / launchctl another try

So while I was mostly settled with my setup there was one problem:
I do not know which signal my processes (my bots) receive when I reboot my machine.
Which is why I can’t properly close my bots and let them do the clean up (mainly moving log files and deleting the lock files).
After some thinking I got the idea: I only have to make sure that upon login those lock files are deleted. But crontab / cronjobs wasn’t done for this purpose.
Maybe give launchd / launchctl another try?

-rwxr–r–@  1 root     wheel   599 Feb  2 19:25 deleteLockFiles.plist*


So you need to put a PLIST (not plst, my first attempt was always with plst which did never work) file into the ~/Library/LaunchAgents directory.


Content of PLIST file:

<?xml version=”1.0″ encoding=”UTF-8″?>

<!DOCTYPE plist PUBLIC “-//Apple//DTD PLIST 1.0//EN” “”>

<plist version=”1.0″>


















load the plist file:

sudo launchctl load deleteLckFiles.plist 

unload the plist file:

sudo launchctl load deleteLckFiles.plist

And the shell script itself can look like this:
cd /usually/your/home/directory
ls -d *.lck
rm *.lck
And that’s it.
As always: start slow and easy (remember: Decomplexify problems):
As those background jobs (crontab/launchd/launchctl) do have different “default” paths, this is always a bit a gamble to know where your output files end up and if crontab and launchd can access your scripts
So first:
  • create a simply script just echoing “Hello world”, have the script and the output file in your home directory, just to make sure.
  • then you may move the script to the directory where it finally will be called, if this does not work, you may need to give in the Security System Preference Panel Full Disk Access to the shell
  • and increase the complexity
  • I gave up trying to run my bots via launchd, couldn’t figure out how to define the paths so the python scripts work properly, but my shell scripts run just about fine via launchd / launchctl

bookmark_borderStop Loss Trading explained

With my trading bot project in NodeJS I want to prove one thing:
One can profit even in bearish markets

Stop Loss Trading explained

What most people involved in trading, be it crypto or stock trading, don’t know: You can even in bear markets improve your position.
If you know, or are quite certain, your asset will one day have a high price you would want to have as much of that asset as you can, right?
Here’s the basic logic:
Does the price drop? Sell after you’re about 5-10% in the red. In worst case you made 5% minus. In best case the price of the asset drops further, which means you can buy in again with the money you still have.
Let me illustrate this.
Your asset is at 1 ($/€) and you have 100 ($/€).
You buy the asset, you now have 100 of it, but 0 ($/€) balance.
Price drops to 90 cents. Now you sell, you make a loss, you only receive 90 ($/€) back.
If the price drops furtherto 80 cents, it makes a lot of sense to rebuy in. At 80 cents with 90 ($/€) you now get 112.5 assets.
If the price goes up to 1 ($/€) where you initially bought, you made 12.5% profit!

Even if the price only goes back to 90 cents you’ll still make a small profit.

And even if the price drops 50% from 1 ($/€) to 50 cents. It still would pay off to sell now, wait for a further drop.
If you can buy in again at 40 cents, and the asset will only rise to 60% thereafter, you minimised a potential (had you not sold/rebought) 40% loss to only 25% loss.
Does the asset go up to 1$ however again, then you made AT YOUR initial price already 25% profit! As a matter of fact, would you not sell at 50 cents and just wait for a 25% profit, the asset would need to go to 1.25 ($/€). At that level IF you had sold at 50 cents and rebought at 40 cents, you’d have a whopping 50% profit already!

bookmark_borderTrading Bot on macOS Big Sur: my experiences during the project

This blog is like a daily journal, I will continue to update this post about my findings during the journey. It is more about difficulties or challenges I had to deal with than about the actual code.
While most content is macOS (or Unix) related, I am very confident that the shown approaches will work in a rather similar way in other environments (Windows) as well.
The actual code/approach how to make the trading bot itself I have explained (with docu/code in the documentation of the video)
This project is mainly based on NodeJS, there are parts I am using in Python, cause I had the code already.

Most important Tip

De-complexify problems
Start with something you can handle and increase step by step the complexity of the task.
Run a script manually, before trying to schedule it, to see if the script runs at all. Run first a very simple script scheduled, before you run a complicated script, to see if you can successfully schedule a script.
Feed a tool first manual data, get a feeling for the data structures you need to supply (JSON, Arrays, …). Play around, meaning, feed wrong structures to see the outcome. Only once you know what the tool expects start to feed data automatically.
Check the chapter about bot simulations for another tip how to “de-complexify” problems.


So after a lot of trials and errors, fixing errors, bringing new ones in, due to being tired, I think I have finally found a version which makes me optimistic to make some profit. The simulations I run, with the triggers I chose, came 75% of the times with a profit, ranging up to more than 20% in 2 weeks.
My bot has higher profits in “normal” times, in high volatile times, like end of January where XRP soared heavily, it performed worse, but still in an acceptable area, where few humans would be able to do better without just being lucky. Once I see it performs bad, I simply send a signal to stop trading, yet I do not need to fiddle with scripts/cron etc.
I will leave my bot now running for a few weeks to check how it performs, no interruption anymore.
Feels good, having coded a lot, having learned a lot about macOS, Unix shell scripting, Python, Signals.
If you have any questions, feel free to contact me at @iPinky77 on Twitter.