{"id":51439,"date":"2014-03-30T21:47:14","date_gmt":"2014-03-30T21:47:14","guid":{"rendered":"http:\/\/www.timestored.com\/b\/?p=51439"},"modified":"2014-03-30T22:01:06","modified_gmt":"2014-03-30T22:01:06","slug":"rsi-relative-strength-index-kdb","status":"publish","type":"post","link":"https:\/\/www.timestored.com\/b\/rsi-relative-strength-index-kdb\/","title":{"rendered":"RSI Relative Strength Index in Kdb"},"content":{"rendered":"<p>In this tutorial we are going to recreate <a href=\"http:\/\/stockcharts.com\/help\/doku.php?id=chart_school:technical_indicators:relative_strength_in\">this<\/a> example RSI calculation in q, the language of the kdb database.<\/p>\n<blockquote><p>The relative strength index (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Relative_strength_index\">RSI<\/a>) is a technical indicator used in the analysis of financial markets. It is intended to chart the current and historical strength or weakness of a stock or market based on the closing prices of a recent trading period. The RSI is classified as a momentum oscillator, measuring the velocity and magnitude of directional price movements. Momentum is the rate of the rise or fall in price.<br \/>\n<br \/>The RSI computes momentum as the ratio of higher closes to lower closes: stocks which have had more or stronger positive changes have a higher RSI than stocks which have had more or stronger negative changes.  The RSI is most typically used on a 14 day timeframe, measured on a scale from 0 to 100, with high and low levels marked at 70 and 30, respectively. Shorter or longer timeframes are used for alternately shorter or longer outlooks. More extreme high and low levels\u201480 and 20, or 90 and 10\u2014occur less frequently but indicate stronger momentum.<\/p><\/blockquote>\n<p>.<\/p>\n<h3>Stock Price Time-Series Data<\/h3>\n<p>We are going to use the following example data, you can download the <a href=\"\/files\/rsi.csv\">csv here<\/a> or the <a href=\"\/files\/cs-rsi.xls\">excel version here<\/a>.<\/p>\n<style type=\"text\/css\">\n\ttable.tableizer-table1 {\n\tborder: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif\n\tfont-size: 0.8em;\n\tpadding:1px 4px;\n\tmargin:1px 4px;\n} \n.tableizer-table1 td {\n\tpadding: 0 2px;\n\tmargin: 0 2px;\n\tborder: 1px solid #ccc;\n\tfont-size: 0.8em;\n}\n.tableizer-table1 th {\n\tpadding: 1px 2px;\n\tmargin: 0 2px;\n\tbackground-color: #104E8B; \n\tcolor: #FFF;\n\tfont-weight: bold;\n}\n<\/style>\n<table class=\"tableizer-table1\">\n<tr class=\"tableizer-firstrow\">\n<th>Date<\/th>\n<th>QQQQ Close<\/th>\n<th>Change<\/th>\n<th>Gain<\/th>\n<th>Loss <\/th>\n<th>Avg Gain<\/th>\n<th>Avg Loss<\/th>\n<th>RS<\/th>\n<th>14-day RSI<\/th>\n<\/tr>\n<tr>\n<td>2009-12-14<\/td>\n<td>44.34<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-15<\/td>\n<td>44.09<\/td>\n<td>-0.25<\/td>\n<td>0.00<\/td>\n<td>0.25<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-16<\/td>\n<td>44.15<\/td>\n<td>0.06<\/td>\n<td>0.06<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-17<\/td>\n<td>43.61<\/td>\n<td>-0.54<\/td>\n<td>0.00<\/td>\n<td>0.54<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-18<\/td>\n<td>44.33<\/td>\n<td>0.72<\/td>\n<td>0.72<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-21<\/td>\n<td>44.83<\/td>\n<td>0.50<\/td>\n<td>0.50<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-22<\/td>\n<td>45.10<\/td>\n<td>0.27<\/td>\n<td>0.27<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-23<\/td>\n<td>45.42<\/td>\n<td>0.33<\/td>\n<td>0.33<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-24<\/td>\n<td>45.84<\/td>\n<td>0.42<\/td>\n<td>0.42<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-28<\/td>\n<td>46.08<\/td>\n<td>0.24<\/td>\n<td>0.24<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-29<\/td>\n<td>45.89<\/td>\n<td>-0.19<\/td>\n<td>0.00<\/td>\n<td>0.19<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-30<\/td>\n<td>46.03<\/td>\n<td>0.14<\/td>\n<td>0.14<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2009-12-31<\/td>\n<td>45.61<\/td>\n<td>-0.42<\/td>\n<td>0.00<\/td>\n<td>0.42<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<tr>\n<td>2010-01-04<\/td>\n<td>46.28<\/td>\n<td>0.67<\/td>\n<td>0.67<\/td>\n<td>0.00<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>RS<\/td>\n<td>RSI<\/td>\n<\/tr>\n<tr>\n<td>2010-01-05<\/td>\n<td>46.28<\/td>\n<td>0.00<\/td>\n<td>0.00<\/td>\n<td>0.00<\/td>\n<td>0.24<\/td>\n<td>0.10<\/td>\n<td>2.39<\/td>\n<td>70.53<\/td>\n<\/tr>\n<tr>\n<td>2010-01-06<\/td>\n<td>46.00<\/td>\n<td>-0.28<\/td>\n<td>0.00<\/td>\n<td>0.28<\/td>\n<td>0.22<\/td>\n<td>0.11<\/td>\n<td>1.97<\/td>\n<td>66.32<\/td>\n<\/tr>\n<tr>\n<td>2010-01-07<\/td>\n<td>46.03<\/td>\n<td>0.03<\/td>\n<td>0.03<\/td>\n<td>0.00<\/td>\n<td>0.21<\/td>\n<td>0.10<\/td>\n<td>1.99<\/td>\n<td>66.55<\/td>\n<\/tr>\n<tr>\n<td>2010-01-08<\/td>\n<td>46.41<\/td>\n<td>0.38<\/td>\n<td>0.38<\/td>\n<td>0.00<\/td>\n<td>0.22<\/td>\n<td>0.10<\/td>\n<td>2.27<\/td>\n<td>69.41<\/td>\n<\/tr>\n<tr>\n<td>2010-01-11<\/td>\n<td>46.22<\/td>\n<td>-0.19<\/td>\n<td>0.00<\/td>\n<td>0.19<\/td>\n<td>0.20<\/td>\n<td>0.10<\/td>\n<td>1.97<\/td>\n<td>66.36<\/td>\n<\/tr>\n<tr>\n<td>2010-01-12<\/td>\n<td>45.64<\/td>\n<td>-0.58<\/td>\n<td>0.00<\/td>\n<td>0.58<\/td>\n<td>0.19<\/td>\n<td>0.14<\/td>\n<td>1.38<\/td>\n<td>57.97<\/td>\n<\/tr>\n<tr>\n<td>2010-01-13<\/td>\n<td>46.21<\/td>\n<td>0.57<\/td>\n<td>0.57<\/td>\n<td>0.00<\/td>\n<td>0.22<\/td>\n<td>0.13<\/td>\n<td>1.70<\/td>\n<td>62.93<\/td>\n<\/tr>\n<tr>\n<td>2010-01-14<\/td>\n<td>46.25<\/td>\n<td>0.04<\/td>\n<td>0.04<\/td>\n<td>0.00<\/td>\n<td>0.20<\/td>\n<td>0.12<\/td>\n<td>1.72<\/td>\n<td>63.26<\/td>\n<\/tr>\n<tr>\n<td>2010-01-15<\/td>\n<td>45.71<\/td>\n<td>-0.54<\/td>\n<td>0.00<\/td>\n<td>0.54<\/td>\n<td>0.19<\/td>\n<td>0.15<\/td>\n<td>1.28<\/td>\n<td>56.06<\/td>\n<\/tr>\n<tr>\n<td>2010-01-19<\/td>\n<td>46.45<\/td>\n<td>0.74<\/td>\n<td>0.74<\/td>\n<td>0.00<\/td>\n<td>0.23<\/td>\n<td>0.14<\/td>\n<td>1.66<\/td>\n<td>62.38<\/td>\n<\/tr>\n<tr>\n<td>2010-01-20<\/td>\n<td>45.78<\/td>\n<td>-0.67<\/td>\n<td>0.00<\/td>\n<td>0.67<\/td>\n<td>0.21<\/td>\n<td>0.18<\/td>\n<td>1.21<\/td>\n<td>54.71<\/td>\n<\/tr>\n<tr>\n<td>2010-01-21<\/td>\n<td>45.35<\/td>\n<td>-0.43<\/td>\n<td>0.00<\/td>\n<td>0.43<\/td>\n<td>0.20<\/td>\n<td>0.19<\/td>\n<td>1.02<\/td>\n<td>50.42<\/td>\n<\/tr>\n<tr>\n<td>2010-01-22<\/td>\n<td>44.03<\/td>\n<td>-1.33<\/td>\n<td>0.00<\/td>\n<td>1.33<\/td>\n<td>0.18<\/td>\n<td>0.27<\/td>\n<td>0.67<\/td>\n<td>39.99<\/td>\n<\/tr>\n<tr>\n<td>2010-01-25<\/td>\n<td>44.18<\/td>\n<td>0.15<\/td>\n<td>0.15<\/td>\n<td>0.00<\/td>\n<td>0.18<\/td>\n<td>0.26<\/td>\n<td>0.71<\/td>\n<td>41.46<\/td>\n<\/tr>\n<tr>\n<td>2010-01-26<\/td>\n<td>44.22<\/td>\n<td>0.04<\/td>\n<td>0.04<\/td>\n<td>0.00<\/td>\n<td>0.17<\/td>\n<td>0.24<\/td>\n<td>0.72<\/td>\n<td>41.87<\/td>\n<\/tr>\n<tr>\n<td>2010-01-27<\/td>\n<td>44.57<\/td>\n<td>0.35<\/td>\n<td>0.35<\/td>\n<td>0.00<\/td>\n<td>0.18<\/td>\n<td>0.22<\/td>\n<td>0.83<\/td>\n<td>45.46<\/td>\n<\/tr>\n<tr>\n<td>2010-01-28<\/td>\n<td>43.42<\/td>\n<td>-1.15<\/td>\n<td>0.00<\/td>\n<td>1.15<\/td>\n<td>0.17<\/td>\n<td>0.29<\/td>\n<td>0.59<\/td>\n<td>37.30<\/td>\n<\/tr>\n<tr>\n<td>2010-01-29<\/td>\n<td>42.66<\/td>\n<td>-0.76<\/td>\n<td>0.00<\/td>\n<td>0.76<\/td>\n<td>0.16<\/td>\n<td>0.32<\/td>\n<td>0.49<\/td>\n<td>33.08<\/td>\n<\/tr>\n<tr>\n<td>2010-02-01<\/td>\n<td>43.13<\/td>\n<td>0.47<\/td>\n<td>0.47<\/td>\n<td>0.00<\/td>\n<td>0.18<\/td>\n<td>0.30<\/td>\n<td>0.61<\/td>\n<td>37.77<\/td>\n<\/tr>\n<\/table>\n<h3>RSI Formulas<\/h3>\n<p>The formulas behind the calculations used in the table are:<\/p>\n<ul>\n<li>First Average Gain = Sum of Gains over the past 14 periods \/ 14.<\/li>\n<li>First Average Loss = Sum of Losses over the past 14 periods \/ 14<\/li>\n<\/ul>\n<p>All subsequent gains than the first use the following:<\/p>\n<ul>\n<li><strong>Average Gain<\/strong> = [(previous Average Gain) x 13 + current Gain] \/ 14.<\/li>\n<li><strong>Average Loss<\/strong> = [(previous Average Loss) x 13 + current Loss] \/ 14. <\/li>\n<\/ul>\n<ul>\n<li><strong>RS<\/strong> = Average Gain \/ Average Loss<\/li>\n<li><strong>RSI<\/strong> = 100 &#8211; 100\/(1+RS)<\/li>\n<\/ul>\n<h2>Writing the Analytic in q<\/h2>\n<p>We can load our data (<a href=\"http:\/\/www.timestored.com\/files\/rsi.csv\">rsi.csv<\/a>) in then apply updates at each step to recreate the table above:<\/p>\n\r\n <textarea rows='5' cols='80' class='code' id='qcode-rsi-analytic'>\r\nq)\/ load our data into a table t\r\nhttpGet:{[host;location] N:\"\\r\\n\\r\\n\"; (4+first ss[a;N])_a:(`$\":http:\/\/\",host)\"GET \",location,\" http\/1.0\\r\\nhost:\",host,N};\r\nr:httpGet[\"www.timestored.com\"; \"\/files\/rsi.csv\"];\r\nt:`date`close xcol (\"DFFFFFFFF\";enlist \",\") 0: r;\r\n\r\n\r\nq)\/ add the columns to recreate our table\r\nt:select date,close,change:close-prev close from t;\r\nupdate gain:abs change*change>0, loss:abs change*change<0 from `t;\r\nf:{(14#0Nf),(avg 14#x),{(y+x*13)%14}\\[(sum 14#x)%14;15_x]};\r\nupdate avgGain:f gain,avgLoss:f loss from `t;\r\nupdate rs:avgGain % avgLoss from `t;\r\nupdate rsi:?[avgLoss=0;100;100*rs%(1+rs)] from `t;\r\n\r\nq)\/ check the figures\r\nq)t[13+til 10]\r\ndate       close change gain loss avgGain   avgLoss   rs       rsi\r\n-----------------------------------------------------------------------\r\n2010.01.04 46.28 0.67   0.67 0\r\n2010.01.05 46.28 0      0    0    0.2569231 0.1076923 2.385714 70.46414\r\n2010.01.06 46    -0.28  0    0.28 0.2215306 0.1128571 1.962929 66.24962\r\n....\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-rsi-analytic'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p style='font-size:0.8em'>Code kindly donated by Terry Lynch<\/p>\n<p>Rather than create all the intermediate columns we can create a <strong>calcRsi<\/strong> function like so:<\/p>\n\r\n <textarea rows='5' cols='80' class='code' id='qcode-rsi-analytic-13'>\r\nmavg1:{a:sum[x#y]%x; b:(x-1)%x; a,a b\\(x+1)_y%x};\r\ncalcRsi:{100*rs%1+rs:mavg1[x;y*y>0]%mavg1[x;abs y*(y:y-prev y)<0]};\r\nupdate rsi2:((14#0Nf),calcRsi[14;close]) from `t;\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-rsi-analytic-13'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p>Finally we can visualize our data using the charting functionality of <a href=\"\/qstudio\">qStudio<\/a> (an IDE for kdb):<\/p>\n<div id=\"attachment_51465\" style=\"width: 742px\" class=\"wp-caption alignnone\"><img aria-describedby=\"caption-attachment-51465\" loading=\"lazy\" src=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/rsi-relative-strength-index-stock-chart.png\" alt=\"RSI Relative Strength Index stock chart for QQQQ created using qStudio\" width=\"732\" height=\"322\" class=\"size-full wp-image-51465\" srcset=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/rsi-relative-strength-index-stock-chart.png 732w, https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/rsi-relative-strength-index-stock-chart-300x131.png 300w\" sizes=\"(max-width: 732px) 100vw, 732px\" \/><p id=\"caption-attachment-51465\" class=\"wp-caption-text\">RSI Relative Strength Index stock chart for QQQQ created using <a href=\"\/qstudio\">qStudio<\/a><\/p><\/div>\n<p>Or to plot RSI by itself (similar to original article<\/p>\n<div id=\"attachment_51472\" style=\"width: 532px\" class=\"wp-caption alignnone\"><img aria-describedby=\"caption-attachment-51472\" loading=\"lazy\" src=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/rsi-relative-strength-index-stock-chart-2.png\" alt=\"RSI Line Graph in qStudio\" width=\"522\" height=\"216\" class=\"size-full wp-image-51472\" srcset=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/rsi-relative-strength-index-stock-chart-2.png 522w, https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/rsi-relative-strength-index-stock-chart-2-300x124.png 300w\" sizes=\"(max-width: 522px) 100vw, 522px\" \/><p id=\"caption-attachment-51472\" class=\"wp-caption-text\">RSI Line Graph in <a href=\"\/qstudio\">qStudio<\/a><\/p><\/div>\n<p>Writing kdb analytics such as Relative Strength Index is covered in our <a href=\"\/kdb-training\/\">kdb training course<\/a>, we offer both <a href=\"\/kdb-training\/learn-kdb-public-course\">public kdb training courses<\/a> in New York, London, Asia and <a href=\"\/kdb-training\/customized-onsite-course\">on-site kdb courses<\/a> at your offices, tailored to your needs.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial we are going to recreate this example RSI calculation in q, the language of the kdb database. The relative strength index (RSI) is a technical indicator used in the analysis of financial markets. It is intended to chart the current and historical strength or weakness of a stock or market based on [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0},"categories":[2,92,23],"tags":[62,90,93,25],"_links":{"self":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/51439"}],"collection":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/comments?post=51439"}],"version-history":[{"count":38,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/51439\/revisions"}],"predecessor-version":[{"id":51732,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/51439\/revisions\/51732"}],"wp:attachment":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/media?parent=51439"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/categories?post=51439"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/tags?post=51439"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}