Tutorial #4 - Financial analytics

Lampyre makes it possible to study multiple data arrays from different perspectives.

Researching national open data may significantly enhance and enrich the results of some OSINT investigations, so we recommend considering these sources too, while conducting your investigations. You will find some links to these sources in the comments to this tutorial.

As an example, let’s turn to state purchases and work with their financial part. We’ll analyze financial transactions, their purpose, those who send and receive funds and we’ll look into this data statistics.

We’ll be analyzing financial data of the "Nationam Aeronautics and Space Administration" (NASA) company, which were published at the official USA national portal https://www.usaspending.gov/

But you can easily analyze data of any governmental company, whichever is of your interest.

Figure 1

So first let’s download the .csv file with data on all NASA contacts for the last year in the format and create a new investigation in Lampyre to start working.

Then let’s import this file into Lampyre. For this we need to choose Import in the "Windows" main menu. A pop up window will appear where you can select files to import. You can import into your local repository and use this data in multiple investigations or you can import just to your current investigation.

Figure 2 Figure 3

We’ll import to our current investigation.

Figure 4

If we double click a file in the "Import" window we can take a look at its structure in a table.

Figure 5

First thing, that strikes our attention, is that apart from the agency name column there are also an office name and office code columns in the file. So in order to understand the structure of NASA we should analyze its offices.

Figure 6

To view the imported file in the schema mode it’s necessary to set mapping and to make creation templates. These templates are required for setting the table data type attributes.

To create a template you have to click the "Add creation template" button Button 1 in the bottom panel of the "Requests" window. The "Creation template" window appears.

Let’s add a template which will later show us the structure of the NASA agency. In the "Table" section of the window (in the bottom) you see all the columns of the imported file. We’ll be using them to create a template.

Our main vertex (object) will be the Award agency (in our case here, considering the structure of the file, it may be only NASA). So we click "Add object" in the upper part of the window.

Figure 7

And type in our object name in the right upper part of the "Creation template" window.

Figure 8

We click the default icon to customize it and choose a new icon. Here is what we get in the end:

Figure 9 Figure 10

Then we have to set the attributes of this object or, in other words, map the table columns. Here, our object has one identifying attribute – agency name:

Figure 11

So we set it in the right part of the "Creation template" window: we type in the name, choose the type of the field – in our case it is the string - and drag the awarding_agency_name into the "Columns" field from the lower part of the window. You can also add attributes to your objects by dragging columns straight onto the objects.

Having ticked these 2 parameters Button 2 we indicate that this attribute is the key one and that it will be viewed as a caption to the vertex. This is what it will look like:

Figure 12

Second object in this "Creation template" will be the Office object. Our identifying attribute will be the office code and the office name will be the caption.

Figure 13

Let's set a link between these 2 objects: the Award Agency and the Office. To do this we select both objects by holding Ctrl and clicking them in the order of the link direction. After this, we click "Add link" in the upper panel of the window:

Figure 14

This is what we get:

Figure 15

Our first template is ready, so we click OK in the right lower part of the window. Now we’re ready to build graphs for the imported data, using the Creation template that we’ve set. In the "Import" window we click the uploaded file to select it and then click "Schema" in the window menu.

Figure 16

Or we can just click the "Schema" button in the left side panel of the table with the uploaded data.

Figure 17

Let's change the layout into the Circular one by clicking the "Auto pacement" button Button 3 in the left panel of the schema:

Figure 18

Here is our result:

Figure 19

Let's open the "Content" window (in the Windows main menu drop-down list). All the data on the schema is contained there and we can search it and also group different objects by attributes. Please note that you can work with the "Content" window without creating any schema and that the infomation of your uploaded file can be viewed in this window at any time.

So in our case we see that there are 13 Offices in the data array of our analysis, which deal with financial transactions.

Figure 20 Figure 21

Here we can take a look at other statistics by other parameters:

Figure 22 Figure 23

What is interesting here is the fact that the companies are divided into categories by their owner's ethnicity and gender. So we can statistically assess the participation of certain ethnical groups in the procurement for government, see the volumes of the concluded contracts by so-called "type" of the company owner and compare them.

Figure 24

Let’s edit our graph creation template and add sub agencies, which financed the transactions, to our schema. This will show us which office is connected to which sub agency. We click the "Edit creation template" button Button 4 in the bottom of the "Import" window, then select the template that we want to change and click "Edit":

Figure 25

We’ll add the funding_sub_agency_name to the existing schema the same way as we did with the Office and Award agency objects. We click "Add object" in the upper part of the window, we set the name, the icon, we do the mapping:

Figure 26

This newly created object has only one attribute, which is the key attribute (similar objects will be united by this attribute). Also we'll be viewing this same attribute as a caption to our object on the graph.

Then let's set up a link between the Office and the funding_sub_agency_name, similar to the way we did for the objects, and then we click "OK" in the right bottom of the window:

Figure 27

Let's create a schema by our new template:

Figure 28 Figure 29

So here we see that most of the sub agency objects, including those of military sort, work with Office "Jet Propulsion Laboratory" (NASA MANAGEMENT OFFICE -- JPL).

If we add the funding_office_name to our creation template the schema will become even more detailed. So we open the template editing once again:

Figure 25

And add a new object - funding_office_name. We set its icon and its attributes:

Figure 30

It's going to have 2 attributes: funding_office_name and funding_office_code. Funding_office_code will be the key attribute and funding_office_name will be the caption. We create a link between the funding_office_name and funding_sub_agency_name:

Figure 31

This is what our schema creation template looks like:

Figure 32

We save it and then use it to create a new schema:

Figure 33 Figure 34

All 4 of the objects got to the schema.

Now let's look into the transactions themselves, for which we'll add a new schema creation template first. We'll name it Transactions and add some objects with some attributes to it:

  • The Award agency object having these attribute:
Figure 35
  • The Office object with these attributes:
Figure 36
  • The naics_code object with these attributes:
Figure 37
  • The parent_of_recipient object having these attributes:
Figure 38
  • The Town object with these attribute:
Figure 39
  • The Award object with these attributes:
Figure 40
  • And the last one - The Ordering_period_end_date object with attribute.
Figure 41

We also need to add some simple links without attributes between the Award agency and Office, between the Office and naics_code, the Parent of recipient and Award, and also between the naics_code and the Parent of recipient:

Figure 42 Figure 43 Figure 44

The links between the Office and the Town and between the Parent of Recipient and the Town will have the same condition: the primary_place_of_performance_country_name field in the data table should not be empty.

Figure 45

In order to set this condition we add a link between 2 objects, then we click it to select it and set a condition in the right bottom part of the window. We choose the required field from the drop down list, we choose "not equal" and leave the value field empty. So with this condition on our graph we'll have only the links to a certain town.

Figure 46

Between the Office and the Parent of recipient we'll add a link with one attribute: award_id_piid. We add this link then click it and set its attribute which will viewed as a caption on our graph:

Figure 47 Figure 48

And as a finishing step we'll add a link between the Award object and the ordering_period_end_time with the condition that the date is not 01.01.0001 (this will allow us to filter incorrect values in the table out):

Figure 49

This is what our creation template looks like:

Figure 50

So when we create a schema using this template we'll learn how the Office cooperates with other companies (in this case – beneficiaries), what goods or services were acquired and also the places where it took place. The transactions are grouped by the award_id.

However it is not very convenient to apply this creation template to all our data as such schema will be just too difficult to work with, so we'll apply it to just one company.

We'll filter our initial data by some mention of the well know Ilon Masc's company – "Space X". "Space Exploration Technologies Corp" as the recipient_parent_name in our table. To di this we look for the recipient_parent_name column in our initial data table, click this sign Button 5 in the header and type in the company name we are filtering:

Figure 51 Figure 52

We tick the company we found and click "OK". The table is filtered right away and now we can create a schema for this data only, using our last schema creation template. So in the "Import" window, in the menu we choose "Schema".

Figure 53

Or we click button in the left side panel of the table.

Figure 54

We choose the Transactions template and we create our schema:

Figure 55

Now we can see that "Space X" works with 2 NASA offices. Also there are contract values (potential total value of award) and the end of works dates (ordering period end date). If in our data file the records, which have same award_id_piid, have different ordering period end dates – it is also indicated on the schema.

It is worth mentioning that when you select some data on your schema the same data is selected in your table. This cross-module feature may be used for example to take a look at the full description of the award object in the table view of the data. To do this we should place the table and the schema windows side by side. We drag the table tab down and when a submenu for placing windows appears we select where we want to place it:

Figure 56

So we choose to place it below the schema. Now when we select objects on our graph we see the same objects highlighted in the table as well:

Figure 57

On our schema we see that there are 2 spots where action took place. Place of performance:

Figure 58

In Brevard is the Canaveral cape and in Hawthorne – "Space X" headquarters.

Lampyre platform has a feature of viewing data in a timeline mode. We're going to use it to analyze the dates of the contracts. In the "Windows" main menu we choose "Timeline". In our case on the timeline we see the end of all works dates:

Figure 59

After briefly taking a look at different statistics of our data in the "Content" window we see that NASA deals not only with the US-owned companies. So let's analyze its foreign interactions. First we filter our data the same way we did before – we'll choose the recipient_country_name in our table data and tick all countries except the US:

Figure 60

We'll create a new schema creation template which will have a country which provides goods or services. Let's name this template Foreign and add the Award agency, office and recipient_country_name as 3 objects. The link between the Award agency and office is a simple one without any attributes and the link between the Office and the recipient_country_name is with one attribute award_id_piid:

Figure 61

We save the template and crate a scheme for all our initial data using it.

Figure 62

Let's change the layout of our graph for a better view by clicking the layout button Button 3 on the schema side panel and choosing a "Circular" layout.

Figure 63

Then let's match the size of the vertices on our graph to the number of their connections by clicking this button Button 6. This is our result:

Figure 64

So on this schema we see the connections of the Agency with foreign countries. So NASA deals with Canada, Great Britain, Sweden and so on. The thickness of the arrows here depends on the number of concluded contracts.

So this way we have fulfilled our task of analyzing the financial transactions of the company that we chose to study. Everyone should decide for himself what conclusions to make and what direction to move for further analysis, should you proceed with trying out your own theories or analytical leads.

You can use this research to accomplish your own analytical tasks. For example, you can study how the sanctions towards China affect the US inner market, using the procurement data of different companies and taking into account the global economic situation. The key point in such investigations is coming up with analytical theories, and Lampyre will be perfect for testing them.