{"id":51336,"date":"2014-03-29T22:27:58","date_gmt":"2014-03-29T22:27:58","guid":{"rendered":"http:\/\/www.timestored.com\/b\/?p=51336"},"modified":"2014-03-30T21:54:55","modified_gmt":"2014-03-30T21:54:55","slug":"exponential-moving-average-ema-kdb","status":"publish","type":"post","link":"https:\/\/www.timestored.com\/b\/exponential-moving-average-ema-kdb\/","title":{"rendered":"Exponential Moving Average EMA in Kdb"},"content":{"rendered":"<p>Let&#8217;s look at how to write moving average analytics in q for the kdb database. As example data  (<a href=\"http:\/\/www.timestored.com\/files\/mcd.csv\">mcd.csv<\/a>)  we are going to use stock price data for McDonalds <a href=\"https:\/\/www.google.co.uk\/finance?q=NYSE%3AMCD\">MCD<\/a>. The below code will download historical stock data for MCD and place it into table t:\n<\/p>\n\r\n <textarea rows='5' cols='80' class='code' id='code-ema-moving-setup'>\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\/mcd.csv\"];\r\nt:reverse (\"DFFFFJF\"; enlist \",\") 0: r;\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('code-ema-moving-setup'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>Simple Moving Average<\/h3>\n<p>The simple moving average can be used to smooth out fluctuating data to identify overall trends and cycles. The simple moving average is the mean of the data points and weights every value in the calculation equally. For example to find the moving average price of a stock for the past ten days, we simply add the daily price for those ten days and divide by ten. This window of size ten days then moves across the dates, using the values within the window to find the average. Here&#8217;s the code in kdb for 10\/20 day moving average and the resultant chart.<\/p>\n<div id=\"attachment_51378\" style=\"width: 773px\" class=\"wp-caption alignnone\"><img aria-describedby=\"caption-attachment-51378\" loading=\"lazy\" src=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/moving-average-stock-price-graph-mcd.png\" alt=\"Simple Moving Average Stock Chart Kdb for MCD\" width=\"763\" height=\"284\" class=\"size-full wp-image-51378\" srcset=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/moving-average-stock-price-graph-mcd.png 763w, https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/moving-average-stock-price-graph-mcd-300x111.png 300w\" sizes=\"(max-width: 763px) 100vw, 763px\" \/><p id=\"caption-attachment-51378\" class=\"wp-caption-text\">Simple Moving Average Stock Chart Kdb (Produced using <a href=\"\/qstudio\">qStudio<\/a>)<\/p><\/div>\n\r\n <textarea rows='5' cols='80' class='code' id='code-mavg-1020'>\r\nselect date,adjClose, moving20Avg:mavg[20;adjClose] ,moving10Avg:mavg[10;adjClose] \r\n    from t where date>.z.d-60\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('code-mavg-1020'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>What Exponential Moving Average is and how to calculate it<\/h3>\n<p>One of the issues with the simple moving average is that it gives every day an equal weighting. For many purposes it makes more sense to give the more recent days a higher weighting, one method of doing this is by using the <strong><a href=\"http:\/\/en.wikipedia.org\/wiki\/Exponential_smoothing#The_exponential_moving_average\">Exponential Moving Average<\/a><\/strong>. This uses an exponentially decreasing weight for dates further in the past.The simplest form of exponential smoothing is given by the formula:<\/p>\n<p><a href=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/exponential-moving-average-formula.png\"><img loading=\"lazy\" src=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/exponential-moving-average-formula.png\" alt=\"exponential-moving-average-formula\" width=\"234\" height=\"21\" class=\"alignnone size-full wp-image-51390\" \/><\/a><\/p>\n<blockquote><p> where \u03b1 is the smoothing factor, and 0 < \u03b1 < 1. In other words, the smoothed statistic st is a simple weighted average of the previous observation xt-1 and the previous smoothed statistic st\u22121.<\/p><\/blockquote>\n<p>This table displays how the various weights\/EMAs are calculated given the values 1,2,3,4,8,10,20 and a <strong>smoothing factor of 0.7<\/strong>: (<a href=\"\/files\/mcd-expected-moving-average.xlsx\">excel spreadsheet<\/a>)<\/p>\n<style type=\"text\/css\">\n\ttable.tableizer-table {\n\tborder: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif\n\tfont-size: 12px;\n} \n.tableizer-table td {\n\tpadding: 4px;\n\tmargin: 3px;\n\tborder: 1px solid #ccc;\n}\n.tableizer-table th {\n\tbackground-color: #104E8B; \n\tcolor: #FFF;\n\tfont-weight: bold;\n}\n<\/style>\n<table class=\"tableizer-table\">\n<tr class=\"tableizer-firstrow\">\n<th>Values<\/th>\n<th>EMA<\/th>\n<th>&nbsp;<\/th>\n<th>Power<\/th>\n<th>Weight<\/th>\n<th>Power*Weight<\/th>\n<th>&nbsp;<\/th>\n<th>EMA (text using previous value)<\/th>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>1<\/td>\n<td>&nbsp;<\/td>\n<td>6<\/td>\n<td>0.0005103<\/td>\n<td>0.0005103<\/td>\n<td>&nbsp;<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>1.7<\/td>\n<td>&nbsp;<\/td>\n<td>5<\/td>\n<td>0.001701<\/td>\n<td>0.003402<\/td>\n<td>&nbsp;<\/td>\n<td>(0.7*2)+(0.3*1)<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>2.61<\/td>\n<td>&nbsp;<\/td>\n<td>4<\/td>\n<td>0.00567<\/td>\n<td>0.01701<\/td>\n<td>&nbsp;<\/td>\n<td>(0.7*3)+(0.3*1.7)<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>3.583<\/td>\n<td>&nbsp;<\/td>\n<td>3<\/td>\n<td>0.0189<\/td>\n<td>0.0756<\/td>\n<td>&nbsp;<\/td>\n<td>(0.7*4)+(0.3*2.61)<\/td>\n<\/tr>\n<tr>\n<td>8<\/td>\n<td>6.6749<\/td>\n<td>&nbsp;<\/td>\n<td>2<\/td>\n<td>0.063<\/td>\n<td>0.504<\/td>\n<td>&nbsp;<\/td>\n<td>(0.7*8)+(0.3*3.583)<\/td>\n<\/tr>\n<tr>\n<td>10<\/td>\n<td>9.00247<\/td>\n<td>&nbsp;<\/td>\n<td>1<\/td>\n<td>0.21<\/td>\n<td>2.1<\/td>\n<td>&nbsp;<\/td>\n<td>(0.7*10)+(0.3*6.6749)<\/td>\n<\/tr>\n<tr>\n<td>20<\/td>\n<td>16.700741<\/td>\n<td>&nbsp;<\/td>\n<td>0<\/td>\n<td>0.7<\/td>\n<td>14<\/td>\n<td>&nbsp;<\/td>\n<td>(0.7*20)+(0.3*9.00247)<\/td>\n<\/tr>\n<\/table>\n<p>To perform this calculation in kdb we can do the following:<\/p>\n<p>\r\n <textarea rows='5' cols='80' class='code' id='code-ema-1017'>\r\nq)ema:{first[y](1-x)\\x*y}\r\nq)ema[0.7;1 2 3 4 8 10 20]\r\n1 1.7 2.61 3.583 6.6749 9.00247 16.70074\r\n\r\nq)\/ the \\ here is a very specialised shorthand\r\nq)\/ The slightly longer version is:\r\nq)ema:{{z+x*y}\\[first y;1-x;x*y]}\r\nq)ema[0.7;1 2 3 4 8 10 20]\r\n1 1.7 2.61 3.583 6.6749 9.00247 16.70074\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('code-ema-1017'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n<br \/>\n<span style='font-size:0.9em'>(This code was originally posted to the google mail list by Attila, the full discussion can be found <a href=\"https:\/\/groups.google.com\/d\/topic\/personal-kdbplus\/G7FgxzzKfnc\/discussion\">here<\/a>)<\/span><\/p>\n<p>This <a href=\"http:\/\/code.kx.com\/wiki\/Reference\/BackSlash#scan\">backslash adverb<\/a> works as <\/p>\n<blockquote><p>\nThe alternate syntax generalizes to functions of 3 or more arguments where the first argument is used as the initial value and the arguments are corresponding elements from the lists:\n<\/p><\/blockquote>\n\r\n<textarea class='code'  rows='5' cols='80' id='code-kdb-scan-131'>\r\nq){(x;y;z)}\\[0;1 2 3;4 5 6]\r\n0           1 4\r\n0 1 4       2 5\r\n(0 1 4;2;5) 3 6\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('code-kdb-scan-131'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h4>Exponential Moving Average Chart<\/h4>\n<p>Finally we take our formula and apply it to our stock pricing data, allowing us to see the exponential moving average for two different smoothing factors:<\/p>\n\r\n<textarea class='code'  rows='5' cols='80' id='code-ema-908'>\r\nq)ema:{first[y](1-x)\\x*y}\r\nq)select date,adjClose, ema2:ema[0.2;adjClose], \r\n    ema4:ema[0.4;adjClose] from t where date>.z.d-60\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('code-ema-908'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<div id=\"attachment_51413\" style=\"width: 773px\" class=\"wp-caption alignnone\"><img aria-describedby=\"caption-attachment-51413\" loading=\"lazy\" src=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/exponential-moving-average-stock-price-graph-mcd1.png\" alt=\"Exponential Moving Average Stock Price Chart produced using qStudio\" width=\"763\" height=\"284\" class=\"size-full wp-image-51413\" srcset=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/exponential-moving-average-stock-price-graph-mcd1.png 763w, https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2014\/03\/exponential-moving-average-stock-price-graph-mcd1-300x111.png 300w\" sizes=\"(max-width: 763px) 100vw, 763px\" \/><p id=\"caption-attachment-51413\" class=\"wp-caption-text\">Exponential Moving Average Stock Price Chart produced using <a href=\"\/qstudio\">qStudio<\/a><\/p><\/div>\n<p>As you can see with EMA we can prioritize more recent values using a chosen smoothing factor to decide the balance between recent and historical data.<\/p>\n<p>Writing kdb analytics such as Exponential Moving Average is covered in our <a href=\"\/kdb-training\/\">kdb training course<\/a>, we regularly provide training courses in <a href=\"\/kdb-training\/learn-kdb-public-course\">London, New York<\/a>, Asia or our <a href=\"\/kdb-training\/online-kdb-course\">online kdb course<\/a> is available to start right now.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Let&#8217;s look at how to write moving average analytics in q for the kdb database. As example data (mcd.csv) we are going to use stock price data for McDonalds MCD. The below code will download historical stock data for MCD and place it into table t: Simple Moving Average The simple moving average can be [&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,61,90,93],"_links":{"self":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/51336"}],"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=51336"}],"version-history":[{"count":43,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/51336\/revisions"}],"predecessor-version":[{"id":51725,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/51336\/revisions\/51725"}],"wp:attachment":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/media?parent=51336"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/categories?post=51336"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/tags?post=51336"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}