I’ve recently been tasked with inserting data into an 20-year-old 16-bit application to give us a benchmark data set for an updated alternative. In standard investment banking terms, there are deals, to which orders are attached, and there are orders which can have multiple tickets.

An API had been developed for inserting Orders and Tickets but Deals were still being entered manually. I am averse to doing anything repetitive manually, which is how and why I ended up automating in the first place.

As every techie knows all user-generated data starts life in a spreadsheet. It might get stored in a database eventually but for input and updates, it’s a spreadsheet.
Looking at the tools available I had a choice between QTP and writing a bespoke API.
Looking at the data, and armed with the knowledge that QTP and Excel will talk to each other with minimal fuss, I thought it might be worth giving QTP a trial run.
Looking at the application, I figured “there’s no chance of making it work, but I’m paid to solve problems so spend half-a-day on it, and see what happens”.

First, I found that QTP would recognise and start up the application, but that it couldn’t see the splash page, and nor could it recognise anything apart from the main application window on the landing page – A toolbar with bespoke icons.

SystemUtil.Run "C:\BBQA707\BKRUN\wconbk.exe","book5","C:\BBQA707\BKRUN","open"
Dialog("User logon - XCSOFT, EQUTY").WinButton("Continue").Click
If Dialog("Database Count Checker").Exist then
Dialog("Database Count Checker").WinButton("Close").Click
end if

I had a quick chat with the developer, who told me that short-cut keys were enabled so I had QTP throw a key-stroke combination at the main window, and it worked.

'----------------------------------
'Invoke Issues
'----------------------------------
If Window("Bookbuilder 7.07c").Exist Then
Window("Bookbuilder 7.07c").WinObject("SplashPage").Click 58,67
Else
Wait(3)
Window("Bookbuilder 7.07c").WinObject("SplashPage").Click 58,67
End If
'----------------------------------
'Invoke New Deal
'----------------------------------
Wait(1)
Window("Bookbuilder 7.07c").Type micCtrlDwn
Window("Bookbuilder 7.07c").Type micF1
Window("Bookbuilder 7.07c").Type micCtrlUp
Wait(3)


A few more key-strokes were passed until I got to the ticket entry window. QTP happily recognised this window and identified the text-boxes and buttons upon it. Unfortunately, the naming of the text-boxes and buttons was a bit arbitrary and with an app this old, there’s no possibility of getting an update.

I spent 3 or 4 days in that first couple of weeks mapping every field I could find into the object repository.
At the time I thought this was a waste of time, looking at it now I realise it makes everything so much easier from that point forward. This is true to such an extent that the next time I’m QTP’ing something, I’ll start by mapping out the gui.

From there I began to identify the data I’d need, building a spreadsheet of fieldnames and values in a large table. The tests required 300 deals to begin with. So 300 rows with around 120 fields that could potentially be entered.

I found had to be a bit clever about how QTP navigated between tabs on the application. I had a choice between shortcut keys and mouse-clicks – Mouse-clicks are notoriously bad form when automating in this way, but the gui re-defined shortcut keys based on what was entered into the form previously. I went with the shortcut keys and a lot of “case select” statements to keep the flow of data working as intended.


Select Case UCase(DataTable("DealType", dtGlobalSheet))
Case "CONV"
Window("Bookbuilder 7.07c").Type micUp
Case "SHARE"
'Default so nothing to do
Case "GDS"
Window("Bookbuilder 7.07c").Type micDwn
Case "ADS"
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Case "CONVF"
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Case "UNIT"
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Case "CONVP"
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
Window("Bookbuilder 7.07c").Type micDwn
End Select


Finally I mapped the spreadsheet to the script and to statements to enter the data into the application.
Iteration is handled within QTP’s run settings.

The whole process took about 3 weeks to develop end-to-end. There were a few false dawns where I thought I had all the data only to find out when entering an order or a ticket that I’d missed something necessary at the deal level.

In the end I had a script that reads data from a spreadsheet, writes back where appropriate and runs reliably (albeit really slowly) to enter the 300 deals. It’s used following every database refresh to repopulate the base data.

theText = Window("Bookbuilder 7.07c").WinObject("Matrix#8").GetVisibleText
strID = mid(theText,30,5)
strID = trim(strID)
DataTable.GlobalSheet.SetCurrentRow(j)
' DataTable.SetCurrentRow(i)
DataTable.Value("strID", dtGlobalSheet) = strID
DataTable.ExportSheet Filename , "Global"
' DataTable.Export (Filename)

Having spent time with QTP and a non-web-based application, I found it was easy to get to grips with driving data through a spreadsheet. It was a simple enough task to map the gui into the object repository (think time-consuming, rather than difficult). I’m still a long way from being an expert in QTP but I feel confident that with my VB knowledge and an understanding of QTP, I can certainly automate with it.

One of the things I still haven’t done with QTP though is use it for testing. Test Conditions and Checkpoints are still a mystery, maybe next time I’ll look at those.