UITF Historical NAVpU Data – Making them Readily Accessible

The Trust Officers Association of the Philippines (TOAP) maintains a comprehensive website at uitf.com.ph, which is a valuable resource for anyone interested in the performance and Net Asset Values per Unit (NAVpUs) of various Unit Investment Trust Funds (UITFs) managed by banks and financial institutions. The site provides up-to-date NAVpU data, usually available by the next banking day, alongside detailed profiles of each fund and their historical NAVpUs, albeit protected by a reCAPTCHA.

For analysts and investors like myself who require historical NAVpUs for deeper analysis, it would be ideal to have an API or a JSON format feed for seamless data retrieval. Unfortunately, TOAP has not yet provided such a feature, which adds a layer of complexity to data collection.

To address this gap, I have developed a PHP script that automates the downloading of these pages from various banks and financial institutions daily. The script then saves the data in JSON format on a server, making it readily accessible. Currently, I’ve focused on banks where I have UITF accounts (BPI, BDO, MBTC, RCBC, and SBC), but this can be expanded based on utility and demand.

If you find this approach useful, you are welcome to use the script and download the CSV file provided below. This solution aims to streamline the data collection process, ensuring you have the necessary information at your fingertips without manual intervention.

Stay tuned for updates and enhancements to this tool. Let’s make data access easier and more efficient for everyone involved in financial analysis.

What to do with the downloaded CSV file?

You may want to use Power Query to download the CSV file, which should look a lot like below:

Then, in Excel or Power BI’s Power Query, in a blank query enter the following:

let
    Source = Csv.Document(Web.Contents("https://deuts.net/2024/06/uitf-historical-navpu-data-making-them-readily-accessible/?download"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangeType = Table.TransformColumnTypes(PromoteHeaders,{{"Date", type date}, {"Bank", type text}, {"URL", type text}}),
    AddData = Table.AddColumn(ChangeType, "Data", each fnnavpuperbank([URL],[Date],[Bank])),
    CombineData = Table.Combine(AddData[Data])
in
    CombineData

Note that in line 5 of the script, it calls for a function fnnavpuperbank. Therefore, let’s create that function, create a query of the same name from blank, and enter the following:

let
    Source = (JsonURL as text, UITFDate as date, UITFBank as text) => let
        Source = Json.Document(Web.Contents(JsonURL)),
        TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandCol1 = Table.ExpandListColumn(TableFromList, "Column1"),
        ExpandRecords = Table.ExpandRecordColumn(ExpandCol1, "Column1", {"Fund Name", "NAVpu", "ROI-YOY%", "ROI-YTD%"}, {"Fund Name", "NAVpu", "ROI-YOY%", "ROI-YTD%"}),
        FilterNulls = Table.SelectRows(ExpandRecords, each ([Fund Name] <> null)),
        DeleteAsOfs = Table.TransformColumns(FilterNulls, {{"NAVpu", each Text.BeforeDelimiter(_, "*"), type text}}),
        ChangeType = Table.TransformColumnTypes(DeleteAsOfs,{{"Fund Name", type text}, {"NAVpu", type number}, {"ROI-YOY%", type number}, {"ROI-YTD%", type number}}),
        AddBank = Table.AddColumn(ChangeType, "Bank", each UITFBank, type text),
        AddDate = Table.AddColumn(AddBank, "Date", each UITFDate, type date),
        RemoveErrors = Table.RemoveRowsWithErrors(AddDate, {"Fund Name", "NAVpu", "ROI-YOY%", "ROI-YTD%"})
    in
        RemoveErrors
in
    Source

As soon as you load the table to the worksheet (in the case of Excel) or in the Data Model, you’ll have the different funds for all those banks.