Finance

Finance is not just about the management of money. It covers broader subjects that include, accounting, taxation , investments, analysis, etc.

Understanding SharePoint Indexing: When Does SharePoint Automatically Index Columns?

SharePoint automatically indexes columns in several instances:

  1. Primary Key Columns: When you create a list, SharePoint automatically indexes the primary key column (ID field) for faster retrieval.

  2. Unique Columns: Columns marked as unique are automatically indexed to enforce uniqueness.

  3. Lookup Columns: Columns used as lookup columns in other lists are automatically indexed to optimize lookup queries.

  4. Managed Metadata Columns: SharePoint automatically indexes managed metadata columns to enhance performance when filtering or sorting by these columns.

  5. Indexed Columns in Views: Columns included in indexed views are automatically indexed to improve view performance.

  6. Indexed Columns in Large Lists: In large lists, SharePoint automatically indexes frequently used columns to improve performance.

  7. Date and Time Columns: Columns with date and time data types are automatically indexed to optimize date-based queries.

These automatic indexing features help improve performance and efficiency in SharePoint lists and libraries.

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

Encoding and Decoding Data: CSV vs. JSON

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

Actual Budget is the opensource selfhosted alternative to YNAB

Introduction

I used to love YNAB (You Need A Budget), especially the classic desktop version, because of its fantastic features. The app’s zero-based budgeting system, real-time synchronization, and comprehensive financial reports were indispensable in managing my finances effectively. However, when YNAB transitioned to a cloud-only version and stopped supporting the classic desktop app, the sync became spotty. Additionally, the price of YNAB eventually turned me off, prompting me to look for a more affordable alternative. That’s when I discovered Actual Budget, a free and open-source option that offers similar functionalities.

Overview of YNAB

Key Features:
– Zero-based budgeting system
– Goal tracking and progress visualization
– Real-time synchronization across devices
– API for data extraction to tools like Power Query and Power Pivot
– Native mobile (Android and iOS) apps …

Automating Google Sheets Downloads to Excel: A Simple PHP Script

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

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

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

Generate list of all available characters in Power Query

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

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.

BDO PERA Annual Contribution Limit Increase

Following the directive of the BSP, I received an email today from BDO stating that the limit for my annual contribution to my PERA account with them has increased from P100,000 to P200,000.

This is timely — with the elevated inflation rates in the past year or so, we’d better have enough come retirement age. Plus, this is an opportunity to average down faster with our PERA account subscriptions being down in the past 3 years.

Rich Dad Poor Dad – from overrated to trash

After reading the book (or rather, listening to the audiobook) of Rich Dad Poor Dad by Robert Kiyosaki, I thought it was overrated. After listening to the If Books Could Kill podcast episode about it, I realized it was a total piece of trash.

From promoting illegal investment and business activities, to selling MLM or Ponzi scheme products, people should already stop quoting passages from this book. Forget about assets and liabilities, or shaping people’s mindset about it, other than those taught in accounting classes.

Listen to Michael Hobbes & Peter Shamshiri as they break down the faults in this book:

Scroll to Top