Today I will demonstrate how you can use the AL language in Microsoft Business Central to import data exposed by a REST Web Service in JSon format.

As usual, I created a practical example to demonstrate better the concepts.

What REST Web Service we will use:

We have the following website which provides APIs for real time and historical data on stocks, forex (FX), and digital/crypto currencies:

https://www.alphavantage.co/documentation/

In order to use the APIs, you first need to create an API key on that website. I created it a long time ago, therefore I don’t remember the process, but it should be easy if you read on their site.

Then, if you make a call like:

https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=MSFT&apikey=YOUR_API_KEY

The web service gives you price information for the Microsoft stock (in this example):

What we want to Achieve:

Create an extension in Microsoft Business Central that stores information about the Stocks (in a table called Stock) and about Stocks Prices history (in another table called Stock Price). On the Stock List Page, we need two actions:

–> ImportStockData: this action is calling code that does the following: calls the web service to get the response in a Text variable, deserializes the Json text to a native AL JsonToken class and then creates records in table “Stock Price” with the price history(Closing Price) of the selected stock Symbol:

–> Show Stock Price History: opens the page that shows the data imported using the Rest Web Service:

Example of Stock Prices imported in Business Central for MSFT stock:

How can this be Achieved in Business Central using AL language?

–> I will not go through the creation of the tables and pages in AL because it is boring, but at the end of this post you can get all the files from Github if interested.

–> Let’s focus on the interesting part, the import of the data using the REST Web Service. For this I created a codeunit with a global function ImportStockDate that takes as an argument the Symbol code:

procedure ImportStockData(Symbol: Code[10]);
var
   Client: HttpClient;
   ResponseMessage: HttpResponseMessage;
   RString: Text;
   Url: Text;
   APiKey: Text;
   JToken: JsonToken;
   JTokenRates: JsonToken;
   TimeSeries: JsonToken;
   JsonObject: JsonObject;
   JsonObjectRates: JsonObject;
   ListOfDates: List of [Text];
   DateText: TExt;
   ClosingPrice: Decimal;
   DateOfRate: Date;
   LatestDateAlreadyInDatabase: Date;
begin
   APiKey := 'YOUR_API_KEY';
   Url := 'https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=' +
        Symbol + '&apikey=' + ApiKey;

   Client.Get(Url, ResponseMessage);

   If not ResponseMessage.IsSuccessStatusCode() then
       Error('Web service returned error:\\' +
           'Status code: %1\' +
           'Description: %2',
           ResponseMessage.HttpStatusCode(),
           ResponseMessage.ReasonPhrase());

   ResponseMessage.Content().ReadAs(RString);

   JToken.ReadFrom(RString);
   JToken.SelectToken('[' + '''' + 'Time Series (Daily)' + '''' + ']', TimeSeries);

   JsonObject := TimeSeries.AsObject();
   ListOfDates := JsonObject.Keys();
   LatestDateAlreadyInDatabase := GetLatestDateAlreadyInDB(Symbol);

   foreach DateText in ListOfDates do
       IF GetDateFromText(DateText) >= LatestDateAlreadyInDatabase then begin //the price from the latest date is changing during the day!
           JsonObject.SelectToken(DateText, JTokenRates);
           JsonObjectRates := JTokenRates.AsObject();
           ClosingPrice := GetJsonToken(JsonObjectRates, '' + '4. close' + '').AsValue().AsDecimal();
           DateOfRate := GetDateFromText(DateText);
           InsertStockPrice(Symbol, DateOfRate, ClosingPrice);
       end;
end;

Code Explained:

First get the data from the REST Web Service into the TEXT variable RString.
Then select the Time Series (Daily) token and create a Json Object from it.
Then, in order to access the Date of each stock, I created a List of Text values with all the dates from the Json Object.
Loop through this list and for each date get a Json Token with the Prices of that date. If the date already exists in the database skip, because the rate was already imported in the past. But if the date is equal to the latest date in the database, first check if the price changed between the time we last imported the record and the end of the trading day. If the price changed, modify it also in the database.
Get the Closing Price using JSon Path expression (as we did also in above steps).
Insert/Modify the record in the Stock Price table.

Helper Functions:

–> There are also some helper functions to get a Json Token, get the latest date already imported in the database, Insert/Modify the Stock Prices and convert the date string to a Date:

  local procedure GetJsonToken(JsonObject: JsonObject; TokenKey: text) JsonToken: JsonToken
begin
   if not JsonObject.Get(TokenKey, JsonToken) then
       Error('Could not find token with key: %1', TokenKey);
end;

local procedure GetLatestDateAlreadyInDB(StockSymbol: Code[10]): Date
var
   StockPrice: Record StockPrice;
begin
   StockPrice.SetRange(Symbol, StockSymbol);
   IF StockPrice.FindLast() THEN
       EXIT(StockPrice.Date)
   ELSE
       EXIT(0D);
end;

local procedure InsertStockPrice(StockSymbol: Code[10]; Date1: Date; ClosingPrice: Decimal)
var
   StockPrice: Record StockPrice;
begin
   If StockPrice.Get(StockSymbol, Date1) and (StockPrice.ClosingPrice = ClosingPrice) then
       Exit;

   If StockPrice.Symbol = StockSymbol THEN begin
       StockPrice.ClosingPrice := ClosingPrice;
       StockPrice.Modify();
   end else begin
       StockPrice.Init();
       StockPrice.Symbol := StockSymbol;
       StockPrice.Date := Date1;
       StockPrice.ClosingPrice := ClosingPrice;
       StockPrice.Insert();
   end;
end;

local procedure GetDateFromText(DateText: Text): Date
var
   Year: Integer;
   Month: Integer;
   Day: Integer;
begin
   EVALUATE(Year, COPYSTR(DateText, 1, 4));
   EVALUATE(Month, COPYSTR(DateText, 6, 2));
   EVALUATE(Day, COPYSTR(DateText, 9, 2));
   EXIT(DMY2DATE(Day, Month, Year));
end;

References:

If interested in JSonPath Expressions, this is an interesting article:

https://goessner.net/articles/JsonPath/

Download Sample Files:

This was the example for today, you can also see/download the files from my GitHub account:

https://github.com/andreilungu/NAV_AL/tree/master/RESTWebServiceToImportStockPrices

Blog

SRR for Single page application (SPA - Quasar)

AL language in Microsoft Business Central to import data exposed by a REST Web Service in JSon format