tag:blogger.com,1999:blog-27152216936240749612024-03-13T06:02:48.102-07:00Excel VBA, Quantitative, Technical Analysis Research. Finance4TradersSharing investing and online trading ideas and helping traders get started.shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.comBlogger62125tag:blogger.com,1999:blog-2715221693624074961.post-3255123395149980082012-05-04T06:48:00.000-07:002012-05-04T06:59:25.628-07:00Directional Movement - Backtest Results and Implementation IssuesDirectional Movement - Backtest Results and Implementation Issues
Discusses basic issues and results when attempting to back-test the Directional Movement formula.
Data: S&P500 Emini Futures
Futures Contract Rollover: Arbitrarily set on second Friday of every Mar, Jun, Sep and Dec
Period: 28 Dec 2008 - 31 Mar 2012 (Hourly Chart)
VBA Code: Method B of Code
Excel Version: 2010
Note: 1) shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com5tag:blogger.com,1999:blog-2715221693624074961.post-7572232716763782042012-05-01T09:51:00.000-07:002012-05-04T07:00:30.323-07:00Aroon Indicator / Oscillator - Backtest Results and Implementation IssuesAroon Indicator / Oscillator AI / AO - Backtest Results and Implementation Issues
Discusses basic issues and results when attempting to back-test the AI and the AO indicators
Data: S&P500 Emini Futures
Futures Contract Rollover: Arbitrarily set on second Friday of every Mar, Jun, Sep and Dec
Period: 28 Dec 2008 - 31 Mar 2012 (Hourly Chart)
VBA Code: Method B of Code
Excel Version: 2010shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com10tag:blogger.com,1999:blog-2715221693624074961.post-75012483088676067852012-04-29T07:40:00.000-07:002012-04-29T07:45:10.752-07:00Adaptive Price Zone APZ - Backtest Results and Implementation IssuesAdaptive Price Zone APZ - Backtest Results and Implementation Issues
Discusses basic issues and results when attempting to back-test the APZ indicator
Data: S&P500 Emini Futures
Futures Contract Rollover: Arbitrarily set on second Friday of every Mar, Jun, Sep and Dec
Period: 28 Dec 2008 - 31 Mar 2012 (Hourly Chart)
VBA Code: Method B of Code
Excel Version: 2010
Click here for shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com1tag:blogger.com,1999:blog-2715221693624074961.post-27094768964596709472012-04-28T23:12:00.000-07:002012-05-04T07:02:02.813-07:00Accumulation/Distribution Line ADL - Backtest Results and Implementation IssuesAccumulation/Distribution Line (ADL) - Backtest Results and Implementation Issues
Discusses basic issues and results when attempting to back-test the ADL line indicator
Data: S&P500 Emini Futures
Futures Contract Rollover: Arbitrarily set on second Friday of every Mar, Jun, Sep and Dec
Period: 28 Dec 2008 - 31 Mar 2012 (Hourly Chart)
VBA Code: Method B of Code
Excel Version: 2010
shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com18tag:blogger.com,1999:blog-2715221693624074961.post-3454919058958835492009-06-30T23:59:00.001-07:002009-07-26T02:09:46.201-07:00Continued Fractions and the Modified Lentz's MethodThis articles explains how to evaluate continued fractions and offers VBA code for Excel implementationContinued fractions are of the form belowContinued fractions can be used to express certain numbers, such as π. They are also used in calculating bessel functions of fractional orders. A general method to evaluate continued functions is known as the modified Lentz's method.The algorithm is shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com1tag:blogger.com,1999:blog-2715221693624074961.post-84696332509422535752009-06-30T23:59:00.000-07:002009-07-10T12:42:00.698-07:00Time Series Analysis: Univariate and Multivariate DistributionsIntroductory Concepts in Time Series AnalysisA time series refers to a set of data that is ordered by time. Price, volume and return data are three examples of time series. Time series can be discrete or continuous. In a discrete series, the time interval between each observation is an integer value. Continuous series are ordered by real numbers, the entire set of numbers that form the number shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com2tag:blogger.com,1999:blog-2715221693624074961.post-49665398745257333552009-06-30T23:58:00.001-07:002009-07-10T12:57:38.520-07:00Site UpdatesHi (to whoever is reading this),A big welcome for finding my site. This post is a running list of updates and work done on this site, inclusive of any on-going projects so that visitors including you can have a better understanding of the things I am doing. 11 July 09Have not updated this journal for some time. Basically, I spent the last 2 weeks revamping the site layout. I tore the built-in shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com1tag:blogger.com,1999:blog-2715221693624074961.post-13933683881253450102009-06-30T23:58:00.000-07:002009-07-12T01:41:03.502-07:00Signal Processing in TradingFinancial time series such as price data are in general digital signals. They can only take on a set range of values and can only be measured at discrete time intervals. Even tick data is digital. It is merely sampled at very small time intervals, i.e. the machine records the prevailing quote every millisecond and only shows you those that have changed.Visit HERE for introductory information on shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-71688283325985139512009-06-30T23:57:00.000-07:002009-07-10T12:42:48.440-07:00Understanding Technical Indicators: Filters in FinanceOne of the first few questions that I ask myself is what are technical indicators? Are they just a jumble of equations? In fact, most technical indicators are filters. A filter can be viewed as an equation that transforms a time series to another form, such as how moving averages are supposed to smooth a time series. The objective of a filter is to identify and extract certain features, such as shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-14971240316793102062009-06-30T23:56:00.000-07:002009-07-10T12:43:26.525-07:00Quick Basic Info on Math and PhysicsThis post is where all basic information is stored so that you can easily understand what I am talking about.Introduction to Signals1. A signal is a sequence of numbers2. A signal consists is measured along two dimensions – displacement and time.3. An analogue signal is a continuous signal varying in continuous time. Its displacement (from 0) and time can take on real values*. At each infinitely shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-19162696173928545852009-06-30T11:48:00.000-07:002009-07-06T14:59:24.668-07:00Fisher Transform (VBA Code)The code below provides a convenient sub routine to perform the Fisher transform. The FT_1 sub routine uses the high and low prices of your dataset as its inputs. You will also need to specify a number n representing the length of the normalising period in the transformation process. The sub routine produces 5 columns of outputs - the median price, normalised median price, smoothed normalised shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-67424694320932562992009-06-30T11:28:00.000-07:002009-07-05T17:28:03.775-07:00Average Directional Movement RatingAverage Directional Movement Rating ADRComes with formula, calculation steps and VBA codeIntroductionThe average directional movement rating ADR is based on the average directional index ADX. You might want to read about the average directional index before proceeding further. The ADR is calculated as follows:ADR=(current ADX + ADX k periods ago)/2As such, ADR can be seen as an approximate of theshinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-7799387037333313872009-06-30T08:56:00.000-07:002009-07-06T09:57:58.260-07:00Weighted Moving AverageWeighted Moving Average WMAComes with formula, calculation steps and VBA codeThe weighted moving average is calculated by giving each lagging price a smaller weight. The weight decreases at a constant rate from n to 0.WMA = [n x Pricet + (n-1) x Pricet-1 + … + 2 x Pricet-n+2 + Pricet-n+1] / (n + (n-1) + …+ 2 + 1)The divisor (n + (n-1) + …+ 2 + 1) can be calculated using the formula [n x (n+1)] / shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com1tag:blogger.com,1999:blog-2715221693624074961.post-10881131205042030402009-06-30T07:30:00.000-07:002009-07-05T13:29:35.960-07:00Directional Movement IndexDirectional Movement Index DMXComes with formula, calculation steps and VBA codeIntroductionThe directional movement index, DMX is derived from the positive and negative directional movement indicators, +DMI and -DMI. It is calculated as follows.DMX = Absolute of [+DMI - (-DMI)] / [+DMI + (-DMI)]The +DMI is the Welles Wilder's moving average of +DM over n days divided by the average true range shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-63456298798201407542009-06-30T05:29:00.000-07:002009-07-05T13:33:51.559-07:00Directional Movement IndicatorDirectional Movement Indicator DMIComes with formula, calculation steps and VBA codeIntroductionThe directional movement indicator DMI is the directional movement smoothed by the Welles Wilder's moving average normalized by the average true range. Like directional movement, the DMI is made of two studies - Positive DMI and Negative DMI or +DMI and -DMI respectively. Both +DMI and -DMI are behave shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-34377982273144662582009-06-29T15:18:00.000-07:002009-07-05T14:21:20.485-07:00Average True RangeAverage True Range ATRComes with formula, calculation steps and VBA CodeThe average true range is the Welles Wilder's moving average, WWMA of the true range, TR of a period. It is calculated as follows.ATR=WWMA of TRTRt=max of (Hight,Closet-1) - min of (Lowt,Closet-1)ATR=(TRt+(n-1) x (WWMA of TR)t-1) / nVBA CodeMethod A uses functions, while Method B uses sub procedures to calculate APZ. Method Bshinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com1tag:blogger.com,1999:blog-2715221693624074961.post-42746382423962172822009-06-29T14:32:00.000-07:002009-07-05T14:29:48.095-07:00Average Directional IndexAverage Directional Index ADXComes with formula, calculation steps and VBA codeIntroductionThe average directional index, ADX is a relatively complex indicator involving a total of five other technical studies. They are Welles Wilder's moving average, average true range, directional movement, directional movement indicator and directional movement index. The ADX is a measure of trend strength, shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com3tag:blogger.com,1999:blog-2715221693624074961.post-56894026339742279682009-06-29T01:24:00.000-07:002012-04-29T07:46:50.888-07:00Adaptive Price Zone
Adaptive Price Zone APZ
Read on for formula, calculation steps and VBA code. For naive backtest results and findings, visit here
The Adaptive Price Zone APZ indicator is from the Technical Analysis of Stocks & Commodities Magazine, September 2006 article "Trading With An Adpative Price Zone" by Lee Leibfarth. It has an upper and lower boundary and prices are expected to reverse when it shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-86741843018185601132009-06-28T16:52:00.000-07:002009-07-05T14:45:11.590-07:00Chaikin OscillatorChaikin Oscillator COSCComes with formula, calculation steps and VBA codeIntroductionThe Chaikin oscillator is very similar to the Chaikin money flow, except that it is actually the difference between two moving averages of the Accumulation/Distribution line or ADL. Some articles uses a simple moving averge, while some propose that an exponential moving average be used instead. I choose the shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-60978687015914648672009-06-28T13:06:00.000-07:002009-07-05T14:56:56.571-07:00Chaikin Money FlowChaikin Money Flow CMFComes with formula, calculation steps and VBA codeIntroductionThe Chaikin money flow, CMF is calculated asCMF = Sum of (CLV x Volume) for last n periods / Sum of Volume for last n periods, where n is specified by the user. (CLV x Volume) is a proxy of money flow in and out of a stock. The numerator, sum of (CLV x Volume) for the last n periods is actually very similar to shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-38189700968319072822009-06-28T01:19:00.000-07:002012-05-01T10:48:39.011-07:00Automate Excel Web Queries using a Crawler: Downloading Historical Data
In a previous article, I wrote about clearing the cache for your Excel web queries in VBA and disabling background queries. However, that method is less flexible and requires you to specific tables for Excel to extract the data from. Here, I recommend a method to perform web queries using a so-called "XMLHTTP" object, which is potentially much faster than a typical data connection. It downloads shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com2tag:blogger.com,1999:blog-2715221693624074961.post-4154445306939959072009-06-27T12:41:00.000-07:002009-07-05T15:55:13.479-07:00Writing User Defined Functions in VBAWhat is a User Defined Function?A user defined function, UDF is a function created by users to perform custom calculations in Excel and can used like any other Excel built-in functions, such as SUM, AVERAGE, etc. Custom functions can be written in VBA using Visual Basic Editor or in other languages as components of custom Add-Ins. The focus of this article is to write UDFs in VBA only. What are shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-46601052475271595352009-06-26T14:26:00.000-07:002009-07-05T16:07:40.061-07:00Automate Multiple Excel Web Queries: Downloading Historical DataLooping Web Queries Faster and Easier in VBA without the interuptionsThere are two major poblems with using VBA to perform web queries, that will cause Excel to bug out and show the 1004 or its equivalent Error message.1) Excel actually reads from your internet explorer when performing queries. When your cache is full, usually by the 40th to 50th query, your macro will bug out.2) Excel will move shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0tag:blogger.com,1999:blog-2715221693624074961.post-6977986311457105122009-06-24T12:02:00.000-07:002009-07-05T16:19:09.882-07:00Recommended Excel VBA Coding PracticesA successful VBA project usually fulfils five criteria 1) meets user requirements, 2) is flexible, 3) user friendly, 4) optimized for speed and 5) easy to maintain. Good programming practices can improve your productivity as you create effective VBA applications with minimal effort on coding and debugging.Many beginning programmers fall into the trap of not critically examining user requirements shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com1tag:blogger.com,1999:blog-2715221693624074961.post-60549196418737394342009-06-23T12:42:00.000-07:002009-07-05T16:26:43.215-07:00Quant applications in financePricing and Risk ManagementThe price of an asset is supposedly the expected value of its future cashflows minus interest that you will earn for saving your money in Treasury. The challenge lies in figuring out what is 'expected cashflows' in as smart a way as possible. It sounds simple but options and credit derivatives are large pricing challenges. The famed black-scholes model is known for shinjuhttp://www.blogger.com/profile/17674257500182266149noreply@blogger.com0