Yogesh Dhimate

Notes to Myself

Oct 24, 2017 - 2 minute read

Google Spreadsheets and ArrayFormula for XIRR Calculation

I was looking for a simple way to calculate performance of my investments over time. Popular portfolio trackers like Moneycontrol and Value Research did not meet my needs. So I decided to try out Google Spreadsheets. Nice thing with Google Spreadsheets is its support for import and queries. e.g. AMFI India publishes daily NAV’s on their website http://portal.amfiindia.com/spages/NAV1.txt You can import the data programmatically and run a query on it to find desired NAV. I can use following simple query to check the latest NAV of Birla Sunlife Frontline Equity fund, whose symbol is INF209K01YY7

=QUERY(SPLIT(QUERY(importDATA("http://portal.amfiindia.com/spages/NAV1.txt"),
"SELECT * Where Col1 like '%INF209K01YY7%'"),";"), "Select Col5")

Google Spreadsheets obviously supports data from Google Finance with its built in function GOOGLEFINANCE

=GOOGLEFINANCE("SBIN","price")

This will return latest price for State Bank of India. You can also use Stock Exchange assigned ID’s to query the data from Google Finance

=GOOGLEFINANCE("500510","price")

This will return latest price for Larsen and Tubro. However the most killer feature of Google Spreadsheets is its support for ArrayFormula in conjunction with XIRR. Let me explain. Consider following cash flow.

A B C D
1 Transaction Symbol Date
2 Sell Havells 31/12/2017
3 Buy Havells 01/01/2017
4 Buy Havells 06/01/2017

We have to update the XIRR formula to include the new transaction we just added in our cash flow

=XIRR(D2:D4,C2:C4)*100
Symbol XIRR
Havells 4.493

This is very cumbersome to update XIRR formula, if you have a diversified portfolio with 10+ symbols and investing on a regular basis.

A B C D
1 Transaction Symbol Date
2 Sell Havells 31/12/2017
3 Buy Havells 01/01/2017
4 Buy Havells 06/01/2017
2 Sell SBI 31/12/2017
3 Buy SBI 01/01/2017
4 Buy SBI 06/01/2017
Havells =XIRR(D2:D4,C2:C4)*100
SBI =XIRR(D5:D7,C5:C7)*100

You can see for two symbols we have to keep close track of cell numbers etc to accurately calculate XIRR. ArrayFormula provides nice workaround to simplify and automate this process of using XIRR formulas for a growing number of transactions

https://support.google.com/docs/answer/3093275?hl=en

Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

XIRR can be easily combined with ArrayFormula like below

=ArrayFormula(XIRR((B2:B7="HAVELLS")*D2:D7,C2:C7))*100
=ArrayFormula(XIRR((B2:B7="SBI")*D2:D7,C2:C7))*100

As you can see that except the symbol name both the formulas are identical. We can remove the hard coded symbol name and refer to the cell which has symbol name. Using ArrayFormula in the portfolio tracker has simplified maintenance of the spreadsheet and saved quite a few hours for me.

Sep 24, 2017 - 3 minute read - Programming

Automated Testing of Webmethods Services with REST Assured

While testing REST APIs for one of my projects, I found REST Assured. It was perfect, as it took care of low level HTTP calls under the hood, and provided a high-level, easy to use framework to write tests. Not only REST Assured works really well to test webMethods flow services, you can also run these tests as part of Continuous Integration process through Gradle.

You will need following things on your machine. Make sure these are working without any issues.

  • Java JDK 1.8 (link). Set JAVA_HOME. And add JAVA_HOME to your PATH
  • IntelliJ IDEA (link)

We will write automated tests for a simple flow service that adds two numbers.

Flow_Service

Follow following steps to create automated tests.

  • Open IntelliJ IDEA and create a new project

    Create_Project

  • Add following lines in your build.gradle file

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
dependencies {
    testCompile group: 'junit', name: 'junit', version: '4.12'
    testCompile group: 'org.hamcrest', name: 'hamcrest-library', version: '1.3'
    testCompile group: 'org.hamcrest', name: 'hamcrest-core', version: '1.3'
    testCompile 'io.rest-assured:rest-assured:3.0.3'
}

task wrapper(type: Wrapper) {
    gradleVersion = '2.0' //version required
} 

Update_BuildGradle

  • Create new package for our test cases

    Create_Package

  • Create new Java class for our tests.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
package com.dhimate.wm;

import org.junit.Before;
import org.junit.Test;
import io.restassured.RestAssured;
import io.restassured.response.Response;
import java.util.HashMap;
import static io.restassured.RestAssured.given;
import static io.restassured.RestAssured.basic;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.equalTo;

public class AddNumbersTest {

	@Before
	public void setUp() throws Exception {
		RestAssured.baseURI = "http://localhost:5555/invoke";
		RestAssured.authentication = basic("Administrator", "manage");
	}

	@Test
	public void AddNumbersTestSuccess() {
		String requestBody = "{" +
                    "\"num1\":\"10\"," +
                    "\"num2\":\"20\"" +
                    "}";

		Response response = given().
                	header("Content-Type","application/json").
	                body(requestBody).
       			put("Yogesh.flow/addNumbers");

        		response.getBody().prettyPrint();

        		HashMap responseBody = response.jsonPath().get("");

        		assertThat("status code", response.getStatusCode(), equalTo(200));
        		assertThat("sum", responseBody.get("sum"), equalTo("30"));
    	}
}

Here we are just taking advantage of Integration Server’s built in content handler for Content Type - ‘application/json’. For the flow service available on Integration Server, we can simply pass JSON request using REST Assured framework. Integration Server will take care of converting this to IDoc and subsequently process it to return a JSON response back to your test case.

  • Run the tests using Gradle wrapper

Gradle wrapper provides a convenient and easy to use CLI to run your tests. Using Gradle wrapper you can quickly associate your REST Assured tests with your Continuous Integration process.

Gradle_Wrapper

Good thing about using REST Assured and Hamcrest to test your webMethods flow services is, it is completely FREE. You don’t need to buy an expensive automated testing solution to test your flow services.

Further resources to read

  1. REST Assured has comprehensive documentation available to get you started.
  2. Excellent tutorial on Gradle and Hamcrest by Vogella.

Aug 24, 2016 - 5 minute read - Programming

Using Web Services Client adapter in Apama

Software AG Apama provides a SOAP-based Web Services Client adapter to invoke web services from your Apama application. In this post, we will see how to use this adapter in your application.

We will use a free web service available at CDYNE (link) for our application. This service has multiple operations, out of which we will use GetCityWeatherByZIP operation.

Below are the sample request and response messages for this operation, which we will use to describe our event definition in Apama.

Request XML

Request XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
    <soap:envelope 
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soap:body>
        <GetCityWeatherByZip xmlns="http://ws.cdyne.com/WeatherWS/">
        <Zip>43209</Zip>
        </GetCityWeatherByZip>
    </soap:body>
    </soap:envelope>

Response XML

Response XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
    <soap:Envelope 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Body>
        <GetCityWeatherByZIPResponse 
        xmlns="http://ws.cdyne.com/WeatherWS/">
        <GetCityWeatherByZIPResult>
            <Success>true</Success>
            <ResponseText>City Found</ResponseText>
            <State>OH</State>
            <City>Columbus</City>
            <WeatherStationCity>Columbus</WeatherStationCity>
            <WeatherID>14</WeatherID>
            <Description>Cloudy</Description>
            <Temperature>63</Temperature>
            <RelativeHumidity>83</RelativeHumidity>
            <Wind>CALM</Wind>
            <Pressure>29.93R</Pressure>
            <Visibility/>
            <WindChill/>
            <Remarks/>
        </GetCityWeatherByZIPResult>
        </GetCityWeatherByZIPResponse>
    </soap:Body>
    </soap:Envelope>

Let’s begin by creating a new Apama project. We will name it WeatherWebServiceDemo.

While creating the project using ‘New Apama Project Wizard’, select ‘Web Services Client Adapter’ bundle

img

Now let’s describe the event definitions. We will define events such that their structure corresponds directly to the XML structure of the web service message shown above. Such type of mapping is called as ‘Convention based’ mapping where Apama automatically converts the event instance to the request XML structure of the web service, and also converts the web service response XML to an event instance.

The event definition will look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
    event GetCityWeatherByZIPType {
        string ZIP;
    }

    event GetCityWeatherByZIPResultType {
        string Success;
        string ResponseText;
        string State;
        string City;
        string WeatherStationCity;
        string WeatherID;
        string Description;
        string Temperature;
        string RelativeHumidity;
        string Wind;
        string Pressure;
        string Visibility;
        string WindChill;
        string Remarks;
    }

    event GetCityWeatherByZIPResponseType {
        GetCityWeatherByZIPResultType GetCityWeatherByZIPResult;
    }

    event MyRequest {
        GetCityWeatherByZIPType GetCityWeatherByZIP;
    }

    event MyResponse {
        GetCityWeatherByZIPResponseType GetCityWeatherByZIPResponse;
    }

    event MyZIP {
        string ZIP;
    }

Out of these the MyRequest and MyResponse event definitions are wrapper types required for Convention based mapping, whereas MyZIP is our custom event definition which we will use to request weather information for a zip code.

After describing the event definitions, we can import the WSDL and map the event definitions to web service input and output. To do that, open the adapter instance file located in Adapters->WebServices Client Adapter -> instance1 in your project and click on + (plus) to add the operation name, it will bring up the following screen to configure your web service.

img

In this screen click on ‘Add’ operation, and then click on ‘Create New’ to bring the following screen to import the WSDL and select the operation that you want to use.

img

We will only import GetCityWeatherByZIP operation for our application as shown below.

img

After importing the operation select the input and output event types corresponding to the input and output of the operation

img

Once input and output event for the web service is selected, you can perform the mapping of fields to web service parameters

In the input mapping it’s important to select the ‘Convert to XML’ transformation to convert the event structure to XML. You can select this transformation by using ‘Add computed node’ option in your input mapping.

img

The output mapping is straightforward as shown below.

img

Once the mapping is completed you can write your monitor script (EPL) program to call the weather service and then identify the pattern in the data.

Following simple monitor script program will just print the weather information received from web service.

In this script, whenever the correlator engine detects MyZIP event in the queue, it will route the MyRequest event. MyRequest event will in turn call the web service to get the weather information using web service client adapter and receive the response in the form of MyResponse event. Whenever MyResponse event is detected, our script prints the weather information in the log.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
    monitor WeatherServiceMonitor {
        MyRequest myRequest;
        MyResponse myResponse;
        MyZIP myZIP;

        action onload() {

            on all MyZIP(): myZIP {
                print "Received request for ZIP code : " + myZIP.ZIP;
                print "Preparing to call the service " ;
                myRequest.GetCityWeatherByZIP.ZIP := myZIP.ZIP;
                route myRequest;            
            }

            on all MyResponse(): myResponse {
                print 
                    myResponse.GetCityWeatherByZIPResponse.
                    GetCityWeatherByZIPResult.ResponseText 
                + " " 
                +     myResponse.GetCityWeatherByZIPResponse.
                    GetCityWeatherByZIPResult.City
                + " " 
                +     myResponse.GetCityWeatherByZIPResponse.
                    GetCityWeatherByZIPResult.Temperature
                + " " 
                +     myResponse.GetCityWeatherByZIPResponse.
                    GetCityWeatherByZIPResult.Description
                + " " 
                +     myResponse.GetCityWeatherByZIPResponse.
                    GetCityWeatherByZIPResult.Wind; 
            }
        }
    }

Let’s fetch the weather for zip code 43209

1
2
    C:\SoftwareAG\Apama\bin>engine_send
    MyZIP("43209");

img

We can invoke the web service periodically to fetch the weather information using ‘on all wait() ' as shown below. This code snippet will route the MyZIP event every minute (60 seconds) to fetch the weather information.

1
2
3
    on all wait (60.0) {
      MyZIP("43209"); 
    }

Instead of logging the information to a file we can actually use Apama Dashboard to visually monitor the weather fluctuation and any patterns in the data. I will explain how to do that in another post.