Workflow #1

Optimizing Currency Exchange Rate Data Processing for Your Business

In this business scenario, we will guide you through the strategic steps of efficiently handling currency exchange rate data. This includes retrieving data from an external API, refining it into a structured format, creating a tabular representation with selected exchange rates, and customizing it to align with your business needs, all of which can greatly benefit your financial operations.

Script Source (File)

Our first step involves accessing vital currency exchange rate data. To accomplish this, in the script source (file) node we initiate an HTTP GET request to the API endpoint (https://boi.org.il/PublicApi/GetExchangeRates) using the reliable HttpUtil class. This API offers up-to-the-minute exchange rates, encompassing major currencies such as NIS, USD, EUR, and more. After fetching the data, we employ the JsonUtil class to parse the JSON response and isolate the 'exchangeRates' property within the content.

// Make an HTTP request to get exchange rates from the specified API
var request = HttpUtil.get("https://boi.org.il/PublicApi/GetExchangeRates");

// Parse the response body as JSON
var jsonContent = JsonUtil.parse(request.asString().getBody());

// Convert the exchange rates to a JSON string
var exchanges = JsonUtil.stringify(jsonContent.exchangeRates);

// Create a temporary file and write the exchange rates JSON to it
var tempFile = FileUtil.createTempFile("tmp.json", exchanges);

To streamline data management, we employ DataPrep, ensuring information is organized in a structured tabular format. However, some data, such as date information, may not be presented in a readily understandable format, often appearing as "2023-10-25T12:22:05.2247439Z."

Data Prep

Our second step involves data transformation. To enhance readability, we introduce a new 'date' column and employ the Instant class along with its related functions to reformat the date.

// Parse the datetime string into an Instant
var instant = Instant.parse(datetime);

// Get the year, month and day components from the Instant
var year = InstantUtil.getYear(instant);

var month = InstantUtil.getMonth(instant);

var day = InstantUtil.getDayOfMonth(instant);

// Create a LocalDate object using the extracted year, month, and day
var localDate = LocalDate.of(year, month, day);

With this transformation, the data becomes significantly more user-friendly, which is essential for strategic decision-making.

QUERY-IN Memory

*Unrelated to Custom Scripting

Armed with the transformed data, we can now create a comprehensive table to showcase selected exchange rates, tailored to your business requirements. In our example, we designate the current date as 'date' and extract exchange rates for USD, GBP, and EUR.

select current_date as date, t1.rate as dollar, t2.rate as pound, t3.rate as euro 
from DataPrep_5 t1, DataPrep_5 t2, DataPrep_5 t3
where 
t1.date = t2.date 
and t1.date = t3.date
and t1.CURRENCYCODE = 'USD'
and t2.CURRENCYCODE = 'GBP' 
and t3.CURRENCYCODE = 'EUR'

This SQL-like query efficiently joins the DataPrep_5 table based on the 'date' column and filters the results according to currency codes, resulting in an organized and comprehensible tabular format that is crucial for strategic financial analysis.

Data Prep

After connecting a second node with data about our companies' revenue, we can merge these two tables and add a new column to showcase revenue in NIS, depending on the currency type used in the data.

To further enhance your financial insights, you can introduce a new column, 'amountInNIS,' to the table. By applying a customized script, you can calculate and present amounts in New Israeli Shekels (NIS) based on the original currency.

// Convert amount (tamta) to NIS, depending on the currency (tccur) of the initial amount
if (tccur == "USD") { 
    return (tamta * dollar) 
}
else if (tccur == "EUR") { 
    return (tamta * euro) 
}
else if (tcccur == "GBP") { 
    return (tamta * pound) 
}

By following these strategic steps, you can efficiently manage, transform, and analyze currency exchange rate data, providing valuable insights and support for your business operations. This scenario illustrates how scripting can be leveraged to personalize, optimize, and customize your workflow to fit your specific business requirements.