Compare Data without Specifying the Columns

Posted on

private static boolean getDataFromDB() {
try {

String srcTableQuery = getSrcQuery();
String tgtTableQuery = getTgtQuery();

ResultSet srcResultSet = srcStatement.executeQuery(srcTableQuery);
ResultSet tgtResultSet = tgtStatement.executeQuery(tgtTableQuery);

boolean status = false;
while (srcResultSet.next() | tgtResultSet.next())
if (tgtResultSet.getRow() == 0 | srcResultSet.getRow() == 0)

{
break;
} else {
for (int i = 1; i < tgtResultSet.getMetaData().getColumnCount() + 1; i++) {
String columnName = tgtResultSet.getMetaData().getColumnName(i);
for (int j = 1; j < srcResultSet.getMetaData().getColumnCount() + 1; j++)
if (tgtResultSet.getMetaData().getColumnName(i)
.equals(srcResultSet.getMetaData().getColumnName(j))
&& !(srcResultSet.getString(columnName) == null
|| tgtResultSet.getString(columnName) == null))
if (tgtResultSet.getString(columnName).toUpperCase()
.contains(srcResultSet.getString(columnName).toUpperCase())) {
System.out.println(
“Records matched : Actual ” + srcResultSet.getMetaData().getColumnName(i)
+ ” ” + srcResultSet.getString(columnName) + ” Expected: ”
+ tgtResultSet.getMetaData().getColumnName(i) + ” ”
+ tgtResultSet.getString(columnName));
status = true;
} else {
System.out.println(“Records Not matched : Actual ”
+ srcResultSet.getMetaData().getColumnName(i) + ” ”
+ srcResultSet.getString(columnName) + ” Expected: ”
+ tgtResultSet.getMetaData().getColumnName(i) + ” ”
+ tgtResultSet.getString(columnName));
}
}
}
return status;

} catch (Exception e) {
System.out.println(“Exception thrown while comparing data”);
e.printStackTrace();
}
return false;
}

Advertisements

CompareTheResults

Posted on

package com.WellsFargo.wim.Common;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CompareTwoResultSet {
static Statement srcStatement = null;
static Statement tgtStatement = null;
static {
srcStatement = Connect_To_Oracle.connectToDB();
tgtStatement = Connect_To_Oracle.connectToDB();
}

public static boolean getDataFromDB(String odsSql, String udmStgSql) {
boolean isMatched = false;
try {
ResultSet srcResultSet = srcStatement.executeQuery(odsSql);
ResultSet tgtResultSet = tgtStatement.executeQuery(udmStgSql);

while (srcResultSet.next() && tgtResultSet.next()) {

isMatched =compareTwoTableColumn(srcResultSet, tgtResultSet);

}

System.out.println(“COMPARISION PROGRAM COMPLETED…”);

} catch (Exception e) {
System.out.println(“base code”);
e.printStackTrace();
}
return isMatched;
}
private static boolean compareTwoTableColumn(ResultSet srcResultSet, ResultSet tgtResultSet) throws SQLException {

boolean isMatched =true;
// src
String srcsystemid = srcResultSet.getString(“systemid”);
String srcorderstatus = srcResultSet.getString(“orderstatus”);
String srcside = srcResultSet.getString(“side”);
String srcordertotvalue = srcResultSet.getString(“ORDER_TOT_VALUE”);
String srcorderDTvalue = srcResultSet.getString(“ORDER_DT”);
String srcExeDt = srcResultSet.getString(“EXECUTION_DT”);
String srcTraderVal = srcResultSet.getString(“TRADER_NAME”);
String srcBusnsUnit = srcResultSet.getString(“BUSINESS_UNIT”);

// tgt
String tgtsystemid = tgtResultSet.getString(“systemid”);
String tgtorderstatus = tgtResultSet.getString(“orderstatus”);
String tgtside = tgtResultSet.getString(“side”);
String tgtordertotvalue = tgtResultSet.getString(“ORDER_TOT_VALUE”);
String tgtorderDTvalue = tgtResultSet.getString(“ORDER_DT”);
String tgtExeDt = srcResultSet.getString(“EXECUTION_DT”);
String tgtTraderVal = srcResultSet.getString(“TRADER_NAME”);
String tgtBusnsUnit = srcResultSet.getString(“BUSINESS_UNIT”);

if (!(srcsystemid.equals(tgtsystemid) && srcorderstatus.equals(tgtorderstatus) && srcside.equals(tgtside)
&& srcordertotvalue.equals(tgtordertotvalue) && srcorderDTvalue.equals(tgtorderDTvalue)
&& srcExeDt.equals(tgtExeDt) && srcTraderVal.equals(tgtTraderVal)
&& srcBusnsUnit.equals(tgtBusnsUnit))) {

System.out.println(“One of the column value did not match”);
System.out.println(“Soucre Data – Expected”);
System.out.println(“Syetmid =” + srcsystemid + “|” + ” ” + “OrderStaus = ” + srcorderstatus + “|” + ” ”
+ “Side = ” + srcside + “|” + ” ” + “OrderTotalvalue = ” + srcordertotvalue + “|” + “ORDER_DT = ”
+ srcorderDTvalue + “|” + “Execution_Date = ” + srcExeDt + “|” + “Trader_Name = ” + srcTraderVal + “|”
+ “Business_Unit =” + srcBusnsUnit);
System.out.println(“Target Data – Actual”);
System.out.println(“Syetmid = ” + tgtsystemid + “|” + ” ” + “OrderStaus = ” + tgtorderstatus + “|” + ” ”
+ “Side = ” + tgtside + “|” + ” ” + “OrderTotalvalue = ” + tgtordertotvalue + “|” + “ORDER_DT = ”
+ tgtorderDTvalue + “|” + “Execution_Date = ” + tgtExeDt + “|” + “Trader_Name = ” + tgtTraderVal + “|”
+ “Business_Unit = ” + tgtBusnsUnit);
System.out.println();

isMatched =false;
}

return isMatched;
}

}

ReadTheSQLfromExcel

Posted on Updated on

package com.WellsFargo.wim.Common;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.sample.read.BLSKYExchangeExl;
import com.sample.read.ReadExcel;

public class GetTestSQL {

private static final String FILE_NAME = “Actimize_Param_File.xlsx”;

public static List<String> readSQLFromExcel(String modelName) { //bluesky
List<String> returnValue = new ArrayList<String>();
try {

//FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));

InputStream inputStream =ReadExcel.class.
getClassLoader().getResourceAsStream(FILE_NAME);

XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
XSSFSheet datatypeSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = datatypeSheet.rowIterator(); //1000

while (iterator.hasNext()) { //1
int i=1;
Row currentRow = iterator.next();
if (currentRow.getRowNum() == 0) {
continue; // just skip the rows if row number is 0
}

returnValue=readEachRow(i, currentRow,modelName);
if(returnValue.size()>0) {
break;
}

}

} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return returnValue;
}

private static List<String> readEachRow(int i, Row currentRow,String expectedModelName) {
String modelName =currentRow.getCell(i++).getStringCellValue().toString();
List<String> returnValue= new ArrayList<String>();

if(modelName.equalsIgnoreCase(expectedModelName)) {
String odsSql =currentRow.getCell(i++).getStringCellValue().toString();
String udmStgSql =currentRow.getCell(i++).getStringCellValue().toString();
returnValue.add(odsSql);
returnValue.add(udmStgSql);

/*CompareTwoResultSet.getDataFromDB(odsSql, udmStgSql);

//BLSKYExchangeExl.getDataFromDB(odsSql);
*/

}
return returnValue;

}

}

JavaCodetoConnectOracle

Posted on

ConnectToOracle

package com.WellsFargo.wim.Common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Connect_To_Oracle {

public static Statement connectToDB() {
Statement statement = null;
try {

Class.forName(“oracle.jdbc.driver.OracleDriver”);
// create connection object
Connection connection = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:orcl”, “HR”,
“password”);
// create a statement object
statement = connection.createStatement();

} catch (Exception e) {
System.out.println(“Exception thrown while connecting to DB”);
e.printStackTrace();

}

return statement;
}
}

 

Trading Life Cycle – Trade Attributes

Posted on

Hello Guys,

Again let me take an example of buying Apple iPhone through Amazon Marketplace. Once the Order is placed the Amazon will create an Order Ticket that will have below information,

  • PRODUCT Details
  • Date & Time of the Transaction
  • Price Details
  • Quantity
  • Is it a BUY or Sell?
  • The Executive from Amazon who handles this order.
  • Customer Name
  • Seller Details
  • Expected Delivery Dates
  • Updates to the Order if any

Just apply the same logic to Trade Attributes.

Let’s take this example – Bank of Moon (:p) buying 20M valued Equities from Bank of Venus, this trade is placed in the National Stock Exchange India. This is high-level information about the trade, let us break it down.

Nature of the Trade 

Is the Trade between counterparties are

  • Buying or Selling the Shares of one counterparty to another eventually the buyer of the Shares will get the dividend and Seller will increase his Capital to do the business – EQUITY
  • Buying or Selling the assets at the fixed price with some floating or fixed interest as the payout to the buyer of the BOND – FIXED INCOME
  • Buying and Selling the Currency – FOREX Trading

This we call as TRADE TYPE – FXSPOT, FORWARDS, NON DELIVERED FORWARDS are some of it. I will explain each Trade Type in upcoming Posts.

Mandatory Information about the Trade

Every department in the Trade Life Cycle requires information till the Trade Settles.

  • Trade Identifier that was created by Banks internal process per department. This values should be unique.
  • Trade Type – FXSpot
  • Trade Date
  • Execution Time
  • Executed at – Location

Financial Information about the Trade

  • PRICE of the Trade
  • Buy or Sell
  • Product details (USDINR)
  • Under & Accounting Currency details
  • Notional of the Trade (Size of the Trade)

Booking Information if the Trade

  • Who is booking the Trade is it SALESPERSON or the EXECUTING TRADER?
  • Each bank has its own designated area (DESK) to execute the Trade for each Asset Classes
  • BOOK – A trader can trade a Book which is nothing but Record of All Positions held by the Trader.
  • Trader’s information – Trade Name / Location etc.,

COUNTERPARTY INFORMATION of the TRADE

  • Counterparty name 
  • Counterparty Location
  • Settlement Type of Counterparty 
  • Settlement Date

In my next post, I will try to explain multiple Date analogies in the Trading world.

Thanks for reading – please comment and or share it with your friends if you like.

Happy Reading – Asik

Investment Banking – Trade Life Cyle

Posted on

Hello Folks,

My New Website – http://asketltester.com/trading-life-cycle/ please check here for more updates!!

I have not blogged for a couple of years due to the family commitment, now I have time to share my experiences with you all.

okay, let’s go – Many QA working in Investment Banking domain without Business Knowledge (even I was & I am now :p).  So, I myself sneaked into google &  referred few books. Now I am going to blend my day to day experience with the theoretical knowledge.

Let me give you a classic and simple example of how trade happens?

Asik wants to buy an Apple iPhone from Amazon. Here Asik & Amazon are considered as COUNTERPARTIES and the iPhone is considered as PRODUCT that is being traded. Asik places an order by paying $1000 for 1 iPhone. The No of iPhone is nothing but NOTIONAL. The Amazon is an online e-commerce website – this is called the Market Place. So when Asik places an order, he should check the availability and Asik should accept the money that was mentioned in the website here if SIZE & PRICE should match for trade to happen.

After Order is placed, Amazon should accept the ORDER– soon the SIZE & PRICEmatches Amazon will tag that product to Asik, so the TRADE took place.

There are different types of Order & Trade status maintained before the Order gets FILLED (Order Size & price fulfilled and the Order is closed after the Trade completely Fills )

Sample Order & Trade Status – New, Pending New, Pending Cancel, Cancel Request, Cancelled, Filled, Done for the Day (I will explain each status in upcoming posts – Don’t worry :p).

Okay, now why People, Industries, and Investment banks TRADING?

  1. If I want to increase my current then I will go to the market and  BUY STOCK 
  2. I want to get rid of 50% of my stock because I anticipate there will be price Drop – SELL
  3. To make the profit when he/she anticipates there will be the Price spike
  4. To reduce the RISK by considering the future Event.

BUY or SELL is considered as SIDE of the Order/Trade – Buy Trade / Sell Trade or Buy Order / Sell Order.

Who are all the typical Market participants in any Trading activities?

  • PRODUCER or MANUFACTURE
  • CONSUMER or CUSTOMERS
  • SPECULATOR or MARKET WATCHER
  • MARKET MAKERS

WHO IS/ARE PRODUCER?

  • Apple manufacture (producer) has invested 100 $ millions to produce iPhone XI.
  • The entire income of Apple depends on the Total No of units (Size) and price of each unit.
  • Apple can maximize the no of units produced however they can predict little on Price.
  • To avoid the risk, Apple Fixes a Target Price & no of units that will be sold to Apple Dealers

WHO IS/ARE  CONSUMER?

  • Apple wants few thousands of OLED Screens for their new iPhone Model so they are ready to pay more money to Samsung to get the product on time to market.
  • So Samsung reduces his exposure to the OLED Screens to manage the Supply & DemanD

WHO IS/ARE  SPECULATOR?

  • Best Buy Store (Speculator) takes a view on the likely direction of the price change.
  • Best Buys the Apple units on Foward contract when they anticipate the shortage of iPhone units in the US market and supply later.
  • Best Buy sells the Apple units if the anticipated future glut (new products introduced by another vendor)

High-Level Trade Flow

BROKER –> EXCHANGE –> OTC – Overt the Counter.

BROKER 

  • If an Individual wants to buy some EQUITIES from the market, he can’t go directly to the MARKET and BUYSELL the EQUITIES.
  • Because your BUYER or SELLER doesn’t know your creditworthiness
  • You will not have the liquidity of the Equity.

So individuals or small-scale institutional customers will go to Broker for their trading requirements.

EXCHANGE

  • An organized place where anyone can trade any available Products
  • Only EXCHANGES will display BUY & SELL Price of an asset.
  • Makes sure enough liquidity of the Product available in the Exchange
  • Assessing the Counterparty Risk limitations
  • Electronic Traded Exchanges – the Virtual marketplace

Over the Counter – OTC

  • Exchanges are operated for certain hours, if anyone wants to BUY/SELL any products then they have to go to Over the Counter market.
  • Exchanges will not have all the products, because of Product Limitations, one should go to OTC to trade.
  • Only certified people can trade in EXCHANGES, however, OTC any nonmembers also can trade any Products.

Trading Life Cycle will be continued in next Post.

Thanks – Asik

Data warehouse – Test Data Mangement

Posted on Updated on

Test Data Management

Hi All,

Hope all are doing great….it’s very long time since I posted through my blog… my sincere apologies  😦

Happy Reading……:-)

Software-testing-trends-2013

Test Data availability is the one of and most significant issue that will lead to Schedule slippage in DWH projects.

So the Testers and Test Managers should know what all are requirements for the test data and they need to define the test data strategy.

The Test Data Management should cover the below points:

  1. Subset of PROD data catered for Testing Requirements

 Suppose if you are working in an Enhancement projects then you can avail the existing data that is already loaded into warehouse. In this instance you can’t use the data that’s pulled directly from Production as it’s sensitive to the Bank and its Customers.

  1. Mask the data as per regulatory and data privacy requirements 

All the customer information related information should be masked. It is difficult to test the Masked data, suppose if the Name column should accept 20 (CHAR) and if the data is masked then we cannot do the BVA for the masked column.

  1. Fabricate Test Data in case of un availability 

Your source system cannot provide you the data for all your Testing Scenarios. Lack of Test data is the major issue in the Data warehouse projects. A tester should analyze the system and need to create the test data in the form of Insert Scripts. The test data should be extracted correctly by the query before its loaded into the Target tables.

Typical Challenges involved in Test Data Management:

  1. Delay in Schedule

The test data from Upstream should be their UAT else our testing will not effective, Suppose if we are in SIT and our Up Stream have not Completed UAT then our Schedule will be postponed as unavailable of UAT tested data from Up Stream.

  1. Issues to cover Business Cases

Test Data for few Business Scenarios cannot be produced in Up Stream or through Data fabricating. Take this example, reopening of an Account, a Customer was having an account and he closed the same few years before and his Records are Kept Active in ACCT_STATUS Closed Records. When the Customer comes to Bank and the Bank will somehow Identify his existing account and will try to Reopen across all the tables to stop not having one more record in the warehouse, for this kind of scenarios it’s difficult to manufacture the data in a given time.

  1. Data Privacy & Compliance

Banks are curious about the Customer Data because of the Data theft in any means. So when we will get the data from PROD for our Regression Testing, most of the Customer related data will be masked as per Compliance policy. So we cannot produce the data or test those scenarios in our Environment.

  1. Dependency of SME’s

SME’s availability is a biggest issue in Data warehouse world.

System Architect – He is accountable for the System Design based on the Business Requirements. He will be closely working with Data Modeler (Data Scientist) to design the Source to Target. System Architect needs to fit the current requirement into the Existing or he should create an Application Landscape for the new requirement.

Data Modeler – He/She is accountable for designing the S2T. Data Modeler should know the Physical and Logical Design of the System and Database. For any Data warehouse project, the S2T is the important document.

ETL Developers – He/She should be accountable for High Level and Low Level design of the Requirements into Code. An ETL developer should be capable enough to design the ETL Code without compromising the performance of the Extraction and Lodging mechanism. He/She should know what kind of Transformation mechanism should be designed for the particular requirement.

Test Analyst / Test Managers – Test Managers should foresee all the technical and business requirements can be tested in given time frame, because the Test Data and System Behaviors might be changing which might cause the Schedule slippage.

  1. Availability of Adequate Data

We should start our System Testing with the Source System’s UAT Tested Data. If in case due to some issue if the Source System is not completed their System Testing, and they can provide only their System Tested data, then these data is not sufficient to continue our System Testing, so we need to fabricate the Test Data in order to kick off out Testing.

  1. Volume of Test Data

The Data threshold requirement will be declared by the System Owners. Whereas we cannot create that declared volume of data in our environment and test the performance beyond the threshold limit and below the threshold limit, so there might be Spool Space issues when we go to Production.

  1. Multiple Source & format of data

In case of Multiple Source Systems are participating in ETL, then it’s difficult to get the Data from different source systems on the same time to being our Testing, in this case again we need to create the Mock Test files to being our testing.

 8.     Version Control of Data

Versioning of Data is very difficult in the Data warehouse world whereas we can see the history of the data using few housekeeping columns (EFFT_D, EXPY_D). But the data loads and extracts should be versioned to avoid the confusions.

Cheers,

Asik