Loading header...

Home / DPA Insights / Data Management

From VLOOKUP to a Single Source of Truth

A better way to manage APAC NAV history. How a daily Excel chore became a SQL system, and what the delay taught us.

The short version

NAV history kept in Excel with daily VLOOKUPs turned data control into a daily maintenance chore. A single broken link or one missed formula could compromise the entire file. NAV history is a storage problem, and SQL is built for it: a single table where each row is one fund, one identifier, one date. An SSIS package run by a SQL Server Agent job loads each file off-hours, logs every run and raises an alert when a run fails. The analyst supervises the process rather than operating it. The real lesson: the question was not how to do the work faster, but where the work belonged.

From this buildWhat changed, in numbers
29k APAC share classes. The master file holds more than 108,000 records
30 min daily manual work, now two minutes supervised or zero on the overnight schedule
2.5 hr a week returned to the team: close to a full working day every month

The daily lookup grind

For months, the APAC NAV history we managed lived in Excel. The inventory is drawn from a much larger master file, one that holds more than 108,000 records across every country tracked, and the APAC subset alone runs to about 29,000 share classes. Each day, a VLOOKUP on shared identifiers retrieves the correct NAV for each ISIN out of the main file and into the inventory. It worked, until examined closely.

A structural issue sits beneath this process. Valuations do not arrive for all 29,000 share classes on the same day. If the current valuation date is 3rd July, the master file may still hold 2nd July values for some share classes. Therefore, each day the team must pull the new values, retain the previous day’s where nothing fresh arrived, and backfill any historical dates that were missing earlier. All by hand. A single broken link or one missed formula could compromise the entire file, and what began as data control had turned into a daily maintenance chore.

The fix: one table, one job

The turning point was to stop seeing this as an Excel problem at all. NAV history is a storage problem, and SQL is built for it. The fix was a single table where each row is one fund, one identifier, one date. Every daily file is stored, old records stay untouched, and any point in history can be queried without a rebuild.

Three steps got us there.

  • First, load one daily NAV file through the SSMS Import wizard, which proves the schema and lets you fix date and number formats without writing code.
  • Next, capture the logic and column mappings in an SSIS package with the file path as a parameter, stored centrally by IT.
  • Then hand that package to a SQL Server Agent job, which becomes the engine of the entire process.

That Agent job is where the real gain sits. It executes the package on a schedule, with nobody clicking through a wizard. It runs off-hours and overnight, so the data is ready before the team logs in. Every run is logged, so success or failure is visible at a glance, and if a run fails, an alert is raised immediately rather than surfacing hours later. The same steps run the same way every time, which means the analyst supervises the process rather than operating it.

What changed, in numbers

The numbers tell the story plainly. Daily processing dropped from 30 minutes of manual work to two minutes supervised or zero, on the overnight schedule. A 30-day NAV pull for one ISIN went from minutes of extraction to seconds from a single query. Every row now carries a load timestamp and source filename, making the audit trail automatic rather than reconstructed.

Previously, the manual process consumed about 30 minutes a day. That may sound modest, but it adds up to roughly two and a half hours a week, close to a full working day every month, returned to the team. The team can now answer questions it once avoided. What was this ISIN’s NAV last month? Which records existed as of a given date? Which funds updated late? The audit trail requires no additional effort, since a timestamp and source filename sit on every row.

Table 01 / Excel VLOOKUP versus one table, one job
Excel VLOOKUP One table, one job
What the work is A daily maintenance chore A storage problem SQL is built for
Each day Pull new values, retain yesterday’s, backfill missing dates, all by hand A SQL Server Agent job loads the file off-hours
When it fails A single broken link or missed formula can compromise the entire file Every run is logged; an alert is raised immediately
A 30-day NAV pull for one ISIN Minutes of extraction Seconds from a single query
Audit trail Reconstructed A timestamp and source filename sit on every row
The analyst Operates the process Supervises the process

The question nobody asked for months

The real lesson had little to do with SQL. For months we treated this as a formula problem, so we kept giving it formula answers. Tighter lookups, cleaner links, better version control. None of it was wrong, and none of it helped, because we were occupied with how to do the work faster when the question was where the work belonged.

That question took us months to reach. The workaround held, and a workaround that holds is one nobody goes back to. The cost sat in plain sight as half an hour a day that nobody was counting.

What finally shifted it was time spent on the shape of the data rather than the mechanics of the task. What it was, how it grew, who would need to query it a year later. Those few hours turned out to be worth more than every formula we had written.

In hindsight, the task had stopped being a task long before we noticed. It had become a system we had not yet built, and we spent months maintaining the gap instead of closing it.

Is a workaround holding your fund data together?

If your team still maintains fund data by formula, we can look at the shape of the data with you and where the work belongs, before anyone talks about tooling.

Book a working session

Frequently asked questions

Why move NAV history from Excel to SQL?

NAV history is a storage problem, and SQL is built for it. The fix was a single table where each row is one fund, one identifier, one date. Every daily file is stored, old records stay untouched, and any point in history can be queried without a rebuild.

Why did the daily VLOOKUP process keep breaking down?

Valuations do not arrive for all share classes on the same day. Each day the team had to pull the new values, retain the previous day’s where nothing fresh arrived, and backfill any historical dates that were missing earlier, all by hand. A single broken link or one missed formula could compromise the entire file.

How do you automate a daily NAV file load into SQL Server?

Three steps. First, load one daily NAV file through the SSMS Import wizard, which proves the schema and lets you fix date and number formats without writing code. Next, capture the logic and column mappings in an SSIS package with the file path as a parameter, stored centrally by IT. Then hand that package to a SQL Server Agent job.

What does a SQL Server Agent job add to the process?

It executes the package on a schedule, with nobody clicking through a wizard. It runs off-hours and overnight, so the data is ready before the team logs in. Every run is logged, and if a run fails, an alert is raised immediately rather than surfacing hours later. The analyst supervises the process rather than operating it.

What was the real lesson?

The real lesson had little to do with SQL. The team was occupied with how to do the work faster when the question was where the work belonged. Time spent on the shape of the data, what it was, how it grew and who would need to query it a year later, turned out to be worth more than every formula written.

Shyam R. Naik
Shyam R. Naik

Assistant Manager - Research and Data Operations,

Decimal Point Analytics Pvt Ltd

Shyam R. Naik is Assistant Manager - Research and Data Operations at Decimal Point Analytics. This article is about moving APAC NAV history from a daily Excel VLOOKUP to one SQL table and one Agent job.