Tuesday, October 9, 2012

How to Compare Two Data Sets using Excel



I think it would be very useful to know how we can compare two data sets easily using Ms Excel by using a simple Excel Macro. This can be modified as you wish based on your requirement.
I usually use this simple macro to compare large data sets with in few seconds.

This sample macro is extracted from Microsoft Support web site

Method


  • Start Excel.
  • Press ALT+F11 to start the Visual Basic editor.
  • On the Insert menu, click Module.
  • Enter the following code in a module sheet:
Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("C1:C5")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Book2"). _
    '   Worksheets("Sheet2").Range("C1:C5")
    '
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub
     




  • Press ALT+F11 to return to Excel.
  • Enter the following data (leave column B empty):
A1: 1   B1:     C1: 3
A2: 2   B2:     C2: 5
A3: 3   B3:     C3: 8
A4: 4   B4:     C4: 2
A5: 5   B5:     C5: 0



  • Select the range A1:A5.
  • In Excel 2003 and in earlier versions of Excel, point to Macro on the Tools menu, and then click Macros.
  • In Excel 2007, click the Developer tab, and then click Macro in the Code group.
  • Click Find_Matches, and then click Run.
  • The duplicate numbers are displayed in column B. The matching numbers will be put next to the first column, as illustrated here:



   A1: 1   B1:     C1: 3
   A2: 2   B2: 2   C2: 5
   A3: 3   B3: 3   C3: 8
   A4: 4   B4:     C4: 2
   A5: 5   B5: 5   C5: 0
    



Tuesday, September 25, 2012

How to handle c/c++ memory leaks and access violation issues


Currently I'm working on my M.Sc  Research and I'm implementing my research tools using C++ ( together with  Qt, OpenCV, Boost and Intel TBB libraries). One of the biggest challenges is to overcome the run time exceptions coming due to memory leaks and access violation issues due to threads and poor memory management. Hence I though of posting my personal experience as this would be very helpful to the c/c++ programmers.


Following are the well-known tools that you can use to investigate memory leaks, uninitialized variables, and other run time issues.


and may more you can find via this link


If you are using windows and using Visual Studio then my recommendation is go for VLD ( Visual Leak Detector )

http://vld.codeplex.com/wikipage?title=Using%20Visual%20Leak%20Detector&referringTitle=Documentation

Monday, September 10, 2012

How to setup the slow mode in Selenium RC using Java


How to setup the slow mode in Selenium RC using Java ?

I came across this issue several times when automating test scripts using Selenium RC & Java. In many instances you need to use the slow mode (especially when dealing with the dynamic components) in-order to run your script smoothly.

Following method can be used to set the selenium slow mode by pausing each and every step by limiting in to a specific time period (in milliseconds)


 //define a public string with the time limit you need to pause every step in milliseconds  
 public String timeout="6000";  
 //Add the initial steps to start selenium  
  RemoteControlConfiguration rc = new RemoteControlConfiguration();  
  seleniumserver = new SeleniumServer(rc);  
  selenium = new DefaultSelenium("localhost", 4444, browser, "http://");  
  seleniumserver.start();  
  selenium.start();  
 //Once the server is started then set the default timeout period ( in-order to emulate the slow mode )  
 selenium.setSpeed(timeout);  

Wednesday, August 8, 2012

How to Import Data from Web to an Excel Sheet


In order to import data from web and maintain an excel sheet you can simply use the feature of "Web Query" in the Data Tab in MS Excel.

Steps
1. Create a New Excel File
2. Go to Data > Web Query
3. Enter the URL of the Web Site you need to import data from
4. Then it will indicate the possible fields that you can extract data from using Yellow Arrows
5. Select the fields you want
6. Click on Import
7. Select the Sheet or cell range you need to populate the data
8. Ok

When ever you need to update your data, click on Refresh All which will Sync with the Web Site.

Reference: http://www.mrexcel.com/tip103.shtml



Friday, August 3, 2012

How To Automate Testing using Selenium


What is Selenium ?


"Selenium is a portable software testing framework for web applications. Selenium provides a record/playback tool for authoring tests without learning a test scripting language (Selenium IDE). It also provides a test domain-specific language (Selenese) to write tests in a number of popular programming languages, including C#, Java, Groovy, Perl, PHP, Python and Ruby. The tests can then be run against most modern web browsers. Selenium deploys on Windows, Linux, and Macintosh platforms"  [ Reference: http://en.wikipedia.org/wiki/Selenium_(software) ]

Further you may refer the main web site of Selenium for more details.  [ http://seleniumhq.org/ ]


Selenium IDE



  • As a starting point, it would be nice to first setup Selenium IDE in your Firefox browser ( you can find the xpi from following url :  http://release.seleniumhq.org/selenium-ide/1.9.0/selenium-ide-1.9.0.xpi )
  • Using Selenium IDE you can simply records the steps that you do in the web site and then you can play back them easily. 
  • You may refer this article as a starting guide:  http://seleniumhq.org/docs/02_selenium_ide.html
  • Further you can add assertions to your scripts and verify the values and enhance you scripts.
  • But still if you wish to automate complex work flows in web sites using values from files, xml etc, it is recommended to move to Selenium RC using Java or any language you prefer to write test cases. But still Selenium IDE can used to record the basic flow and then Export to Java (or any supporting language) and then modify them and add more functionality using Java (or the supporting language)

Selenium RC

How to Start Test Automation using Java & Selenium RC

  • Make sure you have downloaded the Selenium Server jar file from above link
  • Record the script using Selenium IDE and export it to Java ( JUNIT )
  • Add the java file to your project
  • Add the Selenium Server jar file to your project as well 
  • Start the selenium server using the command line 
    • Go to the path of the jar file and type  java -jar <selenium server jar name>
  • Run the test case from your Java IDE (eg: inteliJ Idea, Eclipse )
  • You may refer this article for more details http://seleniumhq.org/docs/05_selenium_rc.html
In order to automate a data driven test case using files you may use different techniques to read files from java and then set the values to the relevant places. 

Example: How to read values from Excel files and use in Selenium Test Cases

Selenium Tips
Please refer following links for more tips 







Tuesday, July 31, 2012

Selenium Test Automation for GWT Websites


Hi,

GWT is one of the latest technologies used to develop nice web sites and applications. When it comes to test automation for GWT based web sites / systems we need to know what we can do and what we cannot do. Therefore I thought of sharing this post with what I could found recently and I will keep updating this post with my latest findings. Visitors are welcome to share their experience related to Selenium with GWT under this post.


Following are couple of good articles I came across and looks interesting.

When dealing with different types of elements we may need to use xpath to access some elements in GWT. In that case it is good to have an idea about xpaths and how we can find the xpath and how we can use them  with Selenium.

Following links provide some useful information related to xpath and how we can use in Selenium.



Further I came across following plugins which would be very useful when recording scripts via Selenium IDE

Selenium useful Plugins:
  • Selenium Expert
  • Highlight Elements
  • Implicit Wait

Wednesday, July 25, 2012

How to Generate an Index of Excel Sheets Dynamically



This post is about "How to Generate an Index of Excel Sheets Dynamically" which is very helpful when you are dealing with Large excel files with lots of sheets/tabs.

Step 1. Create a New Sheet called "Index"
Step 2. Right click on the sheet and go to View Code ( It will open VB Editor )
Step 3. Copy the following VB Script there


 Private Sub Worksheet_Activate()
 Dim wSheet As Worksheet
 Dim l As Long
 l = 1
   With Me
     .Columns(1).ClearContents
     .Cells(1, 1) = "INDEX"
     .Cells(1, 1).Name = "Index"
   End With
   For Each wSheet In Worksheets
     If wSheet.Name <> Me.Name Then
       l = l + 1
         With wSheet
           .Range("A1").Name = "Start_" & wSheet.Index
           .Hyperlinks.Add Anchor:=.Range("C1"), Address:="", _
           SubAddress:="Index", TextToDisplay:="Back to Index"
         End With
         Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
         SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
     End If
   Next wSheet
 End Sub


Step 4. Now save and close the VB Editor
Step 5. Go to a different sheet and come back to Index Sheet
Step 6. Now you can see that the Index has been generated along with the list of sheets with links
Step 7. when you go to a sheet you can see another link to go back to index sheet as well.