Archive for category Tutorial

Search turning points in the trading history of a stock

Stock prices fluctuate everyday. It is impossible to pinpoint the bottom or peak of a stock. But by analyzing the trading history of a stock, we can figure a some unusual signals in the trading history of a stock. By analyzing the time series data of the stock trading history, we can easily figure out major turning points. Through analyzing stock data daily, we may be able to watch out when a major turning point appears. The following R program to analyze and visualize turning points in stock trading history. The program is used with MySQL database. The results of data analysis will be stored into the MySQL database and be summarized in the MySQL. After that, summary information will be loaded to R for plotting.  We only present the R script here. The MySQL database structure will not be displayed.

library(tseries) # used for runs.test
library(DBI) # required for RMySQL
library(RMySQL) # for interacting with MySQL database

#=========== functions =============
# execute query on a MySQL database
dbQuery <- function(Query)
  mysql <- dbDriver("MySQL")
  con<-dbConnect(mysql,dbname="stocks",username="username",password="password") # create connection

# read a table from the MySQL database
dbRead <- function(tablename)
  mysql <- dbDriver("MySQL")
  con<-dbConnect(mysql,dbname="stocks",username="username",password="password") # create connection
  myresult <- dbReadTable(con, tablename)

# read stock list from the MySQL database
dbReadStockList <- function(tablename)
  mysql <- dbDriver("MySQL")
  con<-dbConnect(mysql,dbname="stocks",username="username",password="password") # create connection
  mystocks <- dbReadTable(con, tablename)
  dbSendQuery(con,"call drop_stocktables();")

# write analysis result to the MySQL database
dbWrite <- function(tablename, myvar)
  mysql <- dbDriver("MySQL")
  con<-dbConnect(mysql,dbname="stocks",username="username",password="password") # create connection
  dbWriteTable(con, tablename, myvar, overwrite=TRUE)

# conduct the runs test for a given time series
myruns <- function(vobs, n1, n2) 
  n <- nrow(vobs)
  c3 <- rep(-1,n1)
  c4 <- rep(1,n2)

  rank <- c(c3,c4)
  rank <- as.factor(rank)
  dt <- data.frame(vobs,rank)
  dt <- dt[order(dt$vobs),]
  res <- runs.test(dt$rank)
  return(data.frame(statistic=res$statistic, p.value=res$p.value))

# do multiple runs test for a given data set 
# by setting different break point
mytests <- function(obs)
  res <- data.frame(varn=NA, nobs=NA, ndays=NA, date1=NA, date2=NA, varname=NA, varvalue=NA, standardN=NA, p.value=NA)
  nvar <- length(obs)
  nobs <- nrow(obs)
  for (i in 2:(nvar-1))
    vobs <- obs[,i]
    #for (n1 in (as.integer(nobs/2 - nobs/4)+1):(as.integer(nobs/2)))
    for (n1 in 3:5)
      test <- myruns(vobs, n1, nobs-n1)
      res <- rbind(res,data.frame(varn=i, nobs=nrow(obs), ndays=n1, date1=obs$Date[1], date2=obs$Date[n1], varname=colnames(obs[i]), varvalue=vobs[n1], standardN=unname(test$statistic), p.value=unname(test$p.value)))
  res <- res[-1,]

myanalysis <- function(stocksymbol)
  stockdata <- read.csv(paste(stocksymbol,"csv",sep="."))
  stockdata1 <- subset(stockdata, as.POSIXct(stockdata$Date)>=as.POSIXct("2016-10-01"))

  # set data frame for output
  myres <- data.frame(varn=NA, nobs=NA, ndays=NA, date1=NA, date2=NA, varname=NA, varvalue=NA, standardN=NA, p.value=NA)
  # construct data set
  data <- stockdata[1:(nrow(stockdata)-1),]
  data1 <- stockdata[2:nrow(stockdata),]
  data2 <- data
  # compute the up or down values
  for (i in 2:length(data)){ 
    data2[,i] <- data[,i] - data1[,i]
  data <- data2
  while (nrow(data) >= 10)
    ngroups = nrow(data)
    if (ngroups > 20) { ngroups = 20 }
    for (j in seq(10,ngroups,by=2))
      mo <- data[1:j,]
      res1 <- mytests(mo)
      myres <- rbind(myres, res1)
  # romove the latest one 
  data <- data[-1,]

  # remove the first row that includes NAs
  myres <- myrses[-1,]
  # write to csv file
  # write to MySQL data base
  dbWrite(stocksymbol, myres)

#========== Main program ==============
# Search turning point of any stocks automatically 
# import a stock list from the MySQL database
mystocks <- dbReadStockList("_stocks")
# conduct analysis for all stocks
for (stocksymbol in mystocks) 

# summarize turningpoint data
dbQuery("call summarize_turningpoint();")

# look at the general trend of turning point
tpcount <- dbRead("view_tpcount")
tpcount$tp_date <- as.Date(tpcount$tp_date, "%Y-%m-%d")
plot(tpcount$counts ~ tpcount$tp_date, xaxt="n", type="l")
axis(1, tpcount$tp_date, format(tpcount$tp_date, "%b %d"), cex.axis = .7)

# look at the turning point of each company
tpcompanycount <- dbRead("view_tpcompanycount")
tpcompanycount$tp_date <- as.Date(tpcompanycount$tp_date)
tpcompanycount$stocksymbol <- as.factor(tpcompanycount$stocksymbol)
xyplot(countsum ~ tp_date | factor(stocksymbol), data=tpcompanycount, pch=19,
       main="Turning Point Significance Count Sum", xlab="date",  ylab="count",layout=c(3,1),type=c("p","g"))


Tags: , , , ,

Data Analysis of Field Experiments in Agriculture with R

I wrote a series of lectures about analyzing data from agricultural experiments with Minitab long time ago. Since then I learned using SAS and R to analyzing experimental data. In order to share my experiences in data management and analysis in the three software environments I started to put together data analysis, experimental design, and advanced data analysis in SAS, Minitab and R. Since the original series of lectures were written in Chinese, I will put all thing in Chinese first and then in English.


Tags: , , , , ,

Developing Android APP on a Ubuntu 12.04 Desktop Environment

OS: Ubuntu 12.04 64bit version

Setup Android Development Environment

1. Download Android Studio

Download Android Studio by following this link: . Once compressed file is downloaded and unzipped its content to a folder. There is no installation process. That is it. The executable “” is in [android studio foler]/bin/.

2. Install Java Development Kit (JDK 1.7)

Following the instructions in “Installation of Oracle Java SDK 7 on Ubuntu 12.04 (JavaFX is included in the installation)“.  The following steps are from the article.

1) To add our PPA and install the latest Oracle Java (JDK) 7 in Ubuntu, use the commands below:

#sudo add-apt-repository ppa:webupd8team/java
#sudo apt-get update && sudo apt-get install oracle-jdk7-installer

2) Check if Ubuntu uses Java JDK 7

#java -version

If all went right the answer should be something like this:
java version “1.7.0_07″
Java(TM) SE Runtime Environment (build 1.7.0_07-b10)
Java HotSpot(TM) Server VM (build 23.3-b01, mixed mode)

3) Check what compiler is used:

#javac -version

The correct answer should be like this:
javac 1.7.0_07

4)Adding JAVA_HOME to environment

Edit /etc/environment and add JAVA_HOME=/usr/lib/jvm/java-7-oracle to the end of the file.
First open a Terminal (Applications → Accessories → Terminal), then enter:

#sudo gedit /etc/environment

Append to the end of the file:

3. Install 32-bit Java package

To solve the error “Adb not responding” when debugging the Android app, you have to install ia32-libs. Using the following command line to do it.

sudo apt-get install ia32-libs

4. Prepare hardware device for development

Please following the instruction on the android developer site:

5. Create virtual debugging device

Use AVD manager to create and manage virtual devices. Please following the instruction on android developer site:


In Android Studio IDE you can create and debug Android APP easily. All you need to do is create your android app projects and run/debug the app on virtual devices and hardware device. Debugging your APP on real hardware device is necessary because it shows the real performance of your app. Sometime error will not show up on virtual devices but show up on real hardware.


1. Create release APK

In Android Studio, you can easily generate a signed release APK. Below are the steps:

1) Click “View” – “Tool Window” – “Build Variants” to change the build from “debug” to “release”

2) Click “Build” – “Generate Signed APK …” to open the Signed APK generation wizard. In the first screen, simply click “Ok” to move forward to second screen, which provides critical information for generating signed APK. First you need to generate a ‘KEY” to sign the APK. Please read “Generating a Signed Release APK Using a Wizard” for details.

2. Update to a new release version

Use text editor to edit “[project folder]/app/build.gradle”

Change the versionCode and versionName attributes by increasing the versionCode integer to a greater number and change the versionName accordingly to a higher real number. For example, if the original versionCode is 1 and versionName is 1.0, you can change them to 2 and 1.1 respectively. Below is an example of a build.gradle file:

buildscript {
repositories {
dependencies {
classpath ''
apply plugin: 'android'

repositories {

android {
compileSdkVersion 19
buildToolsVersion “19.0.0”

defaultConfig {
minSdkVersion 7
targetSdkVersion 19
versionCode 2
versionName “1.1”
buildTypes {
release {
runProguard false
proguardFiles getDefaultProguardFile(‘proguard-android.txt’), ‘proguard-rules.txt’

dependencies {
compile ‘’
compile ‘’



Tags: , , , , , ,

Use MySQL data table in SpotFire

1 Download and install MySQL Connector / ODBC 5.1

Download MySQL Connector/ODBC 5.1 at site.

Install the package once it is downloaded.

2 Configure MySQL data source

Type “odbc” in the search box of the Windows Start memu. Click “Data source (ODBC)” in the search result to open the ODBC data source administrator windows as the following.



Click “add” in the User DSN tab to configure MySQL data source. Choose “MySQL ODBC 5.1 Driver” in the list can click “Finish” button. Then MySQL Connector / ODBC data source configuration dialog window will appear as the following.


Enter all necessary information for a MySQL database and click “Ok” button to finish. The newly created User DSN should appear in the User Data Source list.

3. Use the MySQL data source in SpotFire

Click File -> Open data table to open Add Data Tables window.

Click “Add” button and choose “Database…” to open Open Database window.


Select “Odbc data provider” and click “Configure…” button to configure data source. Select the newly created MySQL data source in the “System or user data source” list.  Enter server login information if necessary. Then click “Ok” to open the database. Select one or more data tables or views to open data tables.

Once the data added to Spotfire, you can use them to do what you want.


Tags: , ,

Use FTP command line client to copy folders


NcFTP Client (also known as just NcFTP) is a set of FREE application programs implementing the File Transfer Protocol (FTP).The current version is: 3.2.5 (January 17, 2011).

The program has been in service on UNIX systems since 1991 and is a popular alternative to the standard FTP program, /usr/bin/ftpNcFTP offers many ease-of-use and performance enhancements over the stock ftp client, and runs on a wide variety of UNIX platforms as well as operating systems such Microsoft Windows and Apple Mac OS X.

Copy entire folder:

ncftpget -R -v . /data

-R stands for recursive for the entire folder

-v stands for verbose output stands for the ftp server IP address, it can be a URL

. stands for the current folder on the local machine

/data stands for the remote folder on the server