Power Query

UITF Historical NAVpU Data – Making them Readily Accessible

| Finance, Investing | , ,

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. …

UITF Historical NAVpU Data – Making them Readily AccessibleRead More »

Encoding and Decoding Data: CSV vs. JSON

| Finance, Technology | , , , ,

In general, CSV (Comma-Separated Values) is more efficient in encoding a table compared to JSON (JavaScript Object Notation). This is because CSV is a simpler and more lightweight format designed specifically for tabular data.

CSV files are easy to read and write, and they typically consume less storage space compared to JSON due to their minimalistic structure. On the other hand, JSON is designed to store and exchange structured data in a more verbose and human-readable format, which includes key-value pairs and nested structures.

If the primary goal is to store tabular data or exchange data in a format optimized for tables, CSV is usually the more efficient choice. However, JSON provides more flexibility and features for handling complex data structures beyond simple tables. …

Encoding and Decoding Data: CSV vs. JSONRead More »

Automating Google Sheets Downloads to Excel: A Simple PHP Script

| Finance | , ,

In the digital age, managing data efficiently is crucial. Google Sheets, a widely used tool for collaborative data management, sometimes needs to be converted into different formats for further processing. One common requirement is to download a publicly shared Google Sheet as an XLSX file, which can then be used in Excel for advanced data analysis. Here’s a handy PHP script that automates this process.

Why Automate Google Sheets Downloads?

Downloading Google Sheets manually can be tedious, especially if the data is updated frequently. Automating the download process ensures that you always have the latest data in your desired format without repetitive manual effort. Additionally, the XLSX format is compatible with Excel’s powerful features like Power Query, making it easier to transform and analyze data. …

Automating Google Sheets Downloads to Excel: A Simple PHP ScriptRead More »

VBA code to refresh an Excel table generated by a power query

| Finance | , ,

Disclaimer: I’m not good at Excel VBA. That’s why I take advantage of ChatGPT to generate the code for me.

The requirements

Here are my requirements:

  1. When I hit the button, refresh the table called AgingTable in the Aging sheet
  2. The AgingTable  is connected to a Power Query called the same name: AgingTable
  3. Since the AgingTable query is a big table and runs several transformation, it may take a while for the query to finish
  4. However, before proceeding to the next script in the VBA I want it to wait for the query to complete

…

VBA code to refresh an Excel table generated by a power queryRead More »

Generate list of all available characters in Power Query

| Finance |

When you generate a list like for example {1 .. 10}, you’ll get:

1 2 3 4 5 6 7 8 9 10

Or for {"A" .. "Z"} you’ll get:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

What if you want to generate all the available characters in your US English keyboard?

I’m not sure if this is already an exhaustive list, but when I try {"!".. "}"} I’m getting:

! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? @ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _ ` a b c d e f g h i j k l m n o p q r s t u v w x y z { | }

Power Query function to count number of months between two dates

| Finance | ,

Power Query has a built-in Duration.Days function. But what about Duration.Months?

Why don’t we create a custom function for that?

(Date1 as date , Date2 as date) =>
let
    Source =
        List.Generate(
            () => [x = 1, y = Date.AddMonths(Date1,x)],
            each [y] <= Date2,
            each [x = [x] + 1, y = Date.AddMonths(Date1,x)],
            each [y]
        ),
    Months = List.Count(Source)
in
    Months

Note: Date1 should be earlier than Date2.

How to do Lookup in Excel and Power Query

| Finance, Technology | , , , , ,

Lookup in regular Excel function

The XLOOKUP function has been available to Office 365 and Excel 2021. For compatibility purposes with my workbook’s potential users, I shy away from using this function. Instead, I use the combination of INDEX and MATCH.

Why not VLOOKUP?

I’m not sure if there are any hardware efficiency benefit from using INDEX and MATCH over VLOOKUP or HLOOKUP. But I use INDEX and MATCH primarily for two reasons:

  1. Ability to return the value of the column to the left of your lookup array (left-most column of the table array)
  2. Never worry about adding a column between the returned column and the lookup array. Remember, you indicate the column index number being the returned column from your table array? What if you’ve not touched your file for a long time, return to it and inserting a column in a table, forgetting that you’ve used that table as a table array as a lookup somewhere in your worksheet? You’ll certainly break your VLOOKUP formula.

How to use these formulas

Sorry, but you have to look somewhere else for the usage of these formulas.

Lookup in Power Query

Power Query Lookup Sample File

To better demonstrate the functions, it’s better if we refer to a sample file:
…

How to do Lookup in Excel and Power QueryRead More »

How to: Use Excel Power Query to Retrieve Data from Airtable

| Technology | , , ,

When you have your data neatly set up on Airtable, especially when they are particularly financial data and numbers, you would want to connect to them on Excel (or Power BI), and do the analysis (or charts) from the latter.

Excel’s Power Query can ETL (Extract, Transform, and Load) data from your Airtable database, and here’s how to even work around the 100 Rows Limit or Pagination.

What you need are:

  1. Base ID
  2. Table  ID
  3. API Key

So I created a throwaway Airtable account for the API key, a throwaway workspace from that account, and and loaded the Simple Project Tracker base template, just for this demonstration.

You can get your API key from your account page (https://airtable.com/account):

You can get your Base ID and Table ID by going to the base and table you want to connect to and copy them from the URL: …

How to: Use Excel Power Query to Retrieve Data from AirtableRead More »

How to Use Power Query to Pull Data from a CloudFlare Protected Website

| Technology | , , , ,

I run Microsoft Excel’s Power Query daily in order to pull data from the BAP website for the USD/PHP rates for the day, which in turn I save in an Airtable database as a record for that day.

This week, however, it seems that BAP has implemented CloudFlare DDoS Protection, which helps it to combat DDoS attacks. Good for BAP, they can minimize what we’ve experienced downtimes in the past. Bad for me, now I’m getting 503 error in my query.

So how do we deal with this in Power Query? A quick Google search yields not much of a result. There’s one posted in June 2017 on Mr. Excel forum, 3 years and no one bothered with an answer.

…

How to Use Power Query to Pull Data from a CloudFlare Protected WebsiteRead More »

Scroll to Top