Free Software to do Statistical Analysis

Recently I was working on a project which requires analysis of thousands of traffic data. Unfortunately, I have to use Excel since I don't have access to software like SPSS or SAP. Excel gave me a headache because it runs slowly and you cannot easily generate some special graphs and change the settings.
A friend recommended R -a language and environment for statistical computing and graphics developed at Bell Laboratories.
R provides a wide variety of statistical (linear and nonlinear modelling, classical statistical tests, time-series analysis, classification, clustering, ...) and can produce well-designed plots. Users have full control (I like it!).
Most of all, it is Free Software :)

Fileseach Method not available in Office 2007!

Can you believe that?! Application.FileSearch in Excel and Access 2003 has been hidden in Office 2007!!
I hate Microsoft not only because they keep sending updates, patches, and forcing customers to upgrade (to me it's downgrade) Windows version, but also because they make easy things difficult to do!
Microsoft is evil.... stamped... again.

No Function MOD(a,b) in VBA

Can you believe that in VBA you cannot call MOD(a,b) function although it works fine in Excel cells??!
Well, you can use the operator a MOD b if both a and b are integer numbers.
But there is no direct MOD function in VBA because VBA 2003 does not support MOD (number, dividor) as a WorksheetFunction.
Therefore, I have to write the following function:

Function fmod(a As Double, b As Double)
fmod = a - b * (a \ b)
End Function

Don't assume those funcstions are exactly the same!

If you see functions sharing the same name in different programming environments, don't jump to a conclusion that they are exactly the same!
Today it took me a lot of time to find a bug in a VBA macro. I just didn't understand why the function atan2(a,b) cannot return the correct answer.
After carefully reading the description of this function in the Microsoft Visual Basic Help, I realized that I mistakenly assumed that the atan2() in VBA (worksheet functions) is the same as the atan2() in c++. I was wrong!!

In Excel Developer Reference:
Atan2(x,y)
returns the arctangent of the specified x and y coordinates.

In C++ Developer Reference:
C Library, cmath (math.h)

     double atan2 (      double y,      double x );
long double atan2 ( long double y, long double x );
     float atan2 (       float y,       float x );

y: Floating point value representing an y-coordinate.
x: Floating point value representing an x-coordinate

Difference:

See?! The meanings of the first and the second parameters in C++ are switched in VBA!!

Automatically Import XML data into excel at a fixed time interval

'Function: Automatically Import online XML data into  excel at a fixed time interval'
'Language: VBA'
'Author: Vivian N. Yang'
'Date: Dec. 12, 2010'

'Define Global Variables'
'Inputs'
Dim TotalCount As Integer 'Total Number of Data Collections (#)'
Dim TimeInterval As String 'Time Interval to collect data (text format, e.g."00:01:00" means 1 minute)'
Dim Links() As String 'Web Links to collect data'
Dim NumofLinks As Integer 'Number of Links (dimension of the array Links())'
Dim Outputs() As String 'Names of the worksheets to store the outputs'
'Others'
Dim CurrentCount As Integer
Dim SelRange As Range
Dim Actsheet As Worksheet

Sub Main()
' Clear Outputs'
Call Clear_Output
' Get Inputs'
Call Get_Inputs

' Start the data collection'
CurrentCount = 1
Dim XmlMap As XmlMap
For Each XmlMap In ActiveWorkbook.XmlMaps
XmlMap.Delete
Next
' Write Outputs'
Dim i As Integer
For i = 1 To NumofLinks
Sheets(Outputs(i)).Cells(1, 1).NumberFormat = "h:mm:ss AM/PM"
Sheets(Outputs(i)).Cells(1, 1).Value = Format(Now(), "hh:mm:ss")
ActiveWorkbook.XmlImport URL:=Links(i), ImportMap:=Nothing, Overwrite:=True, Destination:=Sheets(Outputs(i)).Cells(2, 1)
Next i

'Start the OnTimeMacro'
Call OnTimeMacro

End Sub

Sub Get_Inputs()
Dim Row As Integer
Dim Col As Integer
Dim i As Integer
Row = 2
Col = 2
NumofLinks = Sheets("Input").Cells(Row, Col).Value
ReDim Links(1 To NumofLinks)
ReDim Outputs(1 To NumofLinks)

For i = 1 To NumofLinks
Row = Row + 1
Links(i) = Sheets("Input").Cells(Row, Col).Value
Outputs(i) = Sheets("Input").Cells(Row, Col + 1).Value
Sheets(Outputs(i)).Cells(1, 1).Value = i
Debug.Print Outputs(i)
Next i
Row = 8
TimeInterval = Sheets("Input").Cells(Row, Col).Value
Row = 9
TotalCount = Sheets("Input").Cells(Row, Col).Value
End Sub


'The time interval can be changed in this subroutine OnTimeMacro()'
'Example: 5 seconds -TimeValue("00:00:05")'
'Example: 1 minute -TimeValue("00:01:00")'

Sub OnTimeMacro()
' Run the RunEvery1Min macro TotalCount times.'
If CurrentCount < TotalCount Then
' Run the RunEvery1Min macro in 1 minute'
'Application.OnTime Now + TimeValue("00:00:10"), "RunEveryTimeInterval"'
Application.OnTime Now + TimeValue(TimeInterval), "RunEveryTimeInterval"
' Increment icount by 1.'
CurrentCount = CurrentCount + 1
Else
' CurrentCount is greater than TotalCount, so exit the macro.'
Exit Sub
End If
End Sub

Sub RunEveryTimeInterval()
Dim Row As Integer
Row = CurrentCount * 7
' Write Outputs'
'Delete all existing XmlMaps'
Dim XmlMap As XmlMap
For Each XmlMap In ActiveWorkbook.XmlMaps
XmlMap.Delete
Next
'Import data'
Dim i As Integer
For i = 1 To NumofLinks
Sheets(Outputs(i)).Cells(Row, 1).NumberFormat = "h:mm:ss AM/PM"
Sheets(Outputs(i)).Cells(Row, 1).Value = Format(Now(), "hh:mm:ss")
ActiveWorkbook.XmlImport URL:=Links(i), ImportMap:=Nothing, Overwrite:=True, Destination:=Sheets(Outputs(i)).Cells(Row + 1, 1)
Next i
' Runs the OnTimeMacro again.'
Call OnTimeMacro

End Sub

C++ String Examples

This website gives good examples about how to use std::string in C++

http://anaturb.net/C/string_exapm.htm

classic C++ books for starters

《The C++ Programming Language》by Bjarne Stroustrup
《C++ Primer》by Stanley B.Lippman and Josee Lajoie

How to read/write Excel in C++

It is not difficult to write/read Excel data in VB/VBA, the development language included with most Microsoft Office products. It's another story if wring such an application using C++.
It seems the most popular method is to use OLE (Object Linking and Embedding) interface. The advantage is that you can use most Excel functions such as formula, format. The disadvantage is obvious, the application relies on Windows.
A class to be used in environments other than Windows would be welcomed by many developers. BasicExcel is such an open-source project. However, as the name indicates, its function is BASIC. You cannot expect too much on complicated applications.

References:
1. Comparison of methods of reading/writing EXCEL in C++ (chinese)
2. How to automate Excel from C++ without using MFC or #import
3. BasicExcel-A Class to Read and Write to Microsoft Excel

C++ I/O Tips 2 Input/Output

Tips and tricks for using C++ I/O (input/output)
(http://www.augustcouncil.com/~tgibson/tutorial/iotips.html)

How to prepare the output stream to print fixed precision numbers (3.40 instead of 3.4)

  std::cout.setf(ios::fixed, ios::floatfield);
  std::cout.setf(ios::showpoint);
  std::cout.precision(2);

How to set the width of a printing field

  Given: int one=4, two=44;
  std::cout << one << std::endl.; 
  //output:  "4"

  std::cout << setw(2) << one << std::endl.;
  //output:  " 4"

  std::cout.fill('X');
  std::cout << setw(2) << one << std::endl.;
  //output:  "X4"

  std::cout.fill('X');
  std::cout << setw(2) << two << std::endl.;
  //output:  "44"

C++ I/O Tip 1: How to skip comments

#include <limits> // for std::numeric_limits
#include <string> // for std::string
#include <iostream> // I/O
#include <fstream>// file I/O

// Remove C/C++ comments
ifstream& SkipComment(ifstream &strm)
{
   char c;
   const int c_MAX_LINESIZE=std::numeric_limits<int>::max();

   if(!strm) return strm;

   while(isspace(c=strm.get())||c==','||c==';');

   if(c=='#'||c=='%'||(c=='/' && strm.peek()=='/'))
   {
     //skip the rest of the line
     strm.ignore(c_MAX_LINESIZE, '\n');
     return strm;
 
   }
   else if (c=='/' && strm.peek()=='*')
   {
     //skip everything in the comment block
     c=strm.get();          //skip the first '*'
     char last='\0';
     while(!(strm.eof())&&strm.good())
     {
       c=strm.get();
       if(c=='/'&&last=='*')break;
       else last=c;
     }
      return strm;
 
 }
 else if(c!=EOF)
 {
     strm.putback(c);
 }

 return strm;
}

//main program
int main(int argc,char **argv)
{
      string IFile="test.dat";
      ifstream ifs( IFile.c_str() );
      if ( ! ifs ){cerr << "Unable to open " << IFile << " for reading\n";}
      SkipComment(ifs)>>x;
      SkipComment(ifs)>>y;
      ifs.close();
      return 0;
}