APIs for Financial Data: Requesting It Programmatically, and Reading What Comes Back

An application programming interface (API) is an address a question is sent to, plus a stated shape the answer arrives in. Asking that address for the Neelbagh stall returns gives back one object for each of the eight stalls, each holding a list of month objects, 32 in all. Flattened, that answer is the same 32 rows the file carries, rupee for rupee.

The address and the shape have to be held apart. Almost every difficulty in requesting a record comes from treating the two halves as one thing. The address is short, easy to read and easy to write down. The shape is long, invisible from the address, and is the half the arithmetic actually stands on. Because the address never says how much of the record it is going to send, an address can be written perfectly and the figure still come out wrong.

Here is the everyday version. There is a ticket window at a municipal office with a printed slip beside it. The ward number and the year go on the slip, the slip is pushed under the glass, and a clerk pushes back a bundle. The slip states what may be asked for. The slip does not state that the clerk hands over one bundle at a time, that the bundles are of unequal thickness, or that a missing return is marked with a stamp rather than left out. All of that lives on the far side of the glass, and every one of those three habits changes what can honestly be said about the papers in hand. An address behaves in exactly the same way.

What is already settled about the Neelbagh record, and what is put on top?

Four pieces of groundwork arrive already done, and not one of them is laid twice. The Neelbagh market, its 32 row stall record and the nine faults sitting inside that record were fixed earlier in the reading order. So was the named conventionA counting rule fixed and recorded ahead of the work, so that anyone repeating it later arrives at the same number instead of an equally arguable one of their own. for the average takings per stall month. The convention totals only those takings cells holding a usable figure, then divides by how many such cells there are. An empty box holds no figure. The office code standing in for a return that never came holds no figure either. A genuine zero does hold one. So, finally, was the habit of stating a row count before an operation and again after it. Nothing leans on that habit as hard as a record delivered a few rows at a time.

One thing only is new: the same record, requested rather than handed over. Nothing about the Neelbagh stall record changes. The shape it arrives in changes, and so does how much of it arrives at once. A delivery that lands a few rows at a time looks complete at every stage, and the arithmetic done on those rows is arithmetic anybody would call correct. Requesting a record is a smaller subject than it sounds, and a more dangerous one.

What is an API, and what actually travels between the analyst and the record?

An API is two things wearing one name. The first is an address: a short line of text naming a record and carrying the question inside it. The second is a shape: a description, stated in advance by whoever published the address, of the words the answer will use, how those words are spelled, and how deeply they sit inside one another. When code reads the answer, it is the shape it depends on, word by word. Change a spelling on the far side and the code stops working, though the address written never altered by a character.

The address used all the way through, an invented one, reads /stall-returns, with a month and a delivery number written into it. A working address carries a host in front of that path, naming the machine that answers it. The path, and whatever follows the question mark, are the part that carries the question.

# the shape of the question, written into the address itself
GET /stall-returns?month=1&delivery=1

# the same address with the month left out, asking for all four months
GET /stall-returns?delivery=1
GET /stall-returns?delivery=2
GET /stall-returns?delivery=3
GET /stall-returns?delivery=4

The address and the four requests that walk the whole record ten rows at a time. GET names the kind of request: a read that asks for something and changes nothing.

What shape does the answer arrive in, and what is JSON made of?

The answer comes back as text in a format called JavaScript Object Notation (JSON), and the whole of that format is built from three things and nothing else. A named value is a word with something attached to it. A list is an ordered run of things inside square brackets. An object is a set of named values inside curly brackets, and any of those values may itself be a list or another object. Named values, lists and objects are the entire vocabulary. Everything ever met in an answer of this kind is those three parts folded into one another.

The Neelbagh answer holds eight stall objects. Each one carries the stall identity, the name the market office wrote, a licence number, a category and a pitch measurement, and then one list called months. The eight month lists hold four, four, five, three, four, four, four and four month objects between them, and those eight counts add to 32. Each month object carries the month, the takings in rupees and the day the return was filed.

// one stall object out of the eight, three of its columns left out for room
{
  "stall_id": "NB-05",
  "stall_name": "Bansi Flour",
  "months": [
    { "month": 1, "takings_rupees": 55000, "filed_on_day": 6 },
    { "month": 2, "takings_rupees": null,  "filed_on_day": 7 },
    { "month": 3, "takings_rupees": 54000, "filed_on_day": 8 },
    { "month": 4, "takings_rupees": 53000, "filed_on_day": 6 }
  ]
}

One of the eight stall objects, printed whole. Bansi Flour filed in all four months but left the takings box empty in month 2, and the answer says so out loud rather than leaving a hole.

Look at what month 2 of Bansi Flour does. In the file that cell is simply empty, and an empty cell is ambiguous: it may mean nothing was earned, or nothing was written, or the line was cut off when somebody exported it. In the answer the same absence arrives as an explicitly empty value sitting in a field that is present. An empty cell is a hole in a record and an explicitly empty value is a statement about the record, and that difference is the one real advantage this shape has over a flat file.

One field on the stall object deserves a second look before anything is computed. The licence number is a whole number, and a whole number invites arithmetic. The licence number is an identifierA value whose entire job is to name one thing so it can be told apart from another. An identifier may be written as digits, but adding two of them together or taking their average produces nothing that means anything., and averaging eight of them gives a figure that is not money, not a size and not a quantity of any kind. The shape gives no warning about this. Nothing in the format distinguishes a number that counts from a number that names.

Why is the answer nested when the file it describes is flat?

A file has one grainWhat a single row of a table actually describes, said as a full sentence. Get it wrong and every count taken afterwards is counting something other than the thing named., and the Neelbagh file has a clear one: one row describes one stall in one month. Because that is the grain, the stall identity, the name, the licence number, the category and the pitch measurement are typed again on every row that stall appears in. Four rows for Bansi Flour, four copies of the words Bansi Flour. The repetition is not a flaw in the file. Repeating the stall details on every row is what flat means.

The answer has two levels instead of one. At the outer level an object describes a stall. At the inner level an object describes one month of that stall. Because the stall sits above the months, its details are written once and the months hang underneath. Neither shape is more correct than the other, and the finance task is never to prefer one, but to get from whichever shape was delivered to the grain the arithmetic needs.

There is a consequence worth pausing on. In the file the stall name is typed on all four rows, and four typings mean four chances to type it differently. Harit Greens appears as Harit Greens in months 1 and 2 and as Harit Green in months 3 and 4, and the file cannot object because each row was typed on its own. In the answer the name is written once per stall object, so one stall cannot disagree with itself. Writing the name once per stall object is a genuine advantage of nesting, and the advantage disappears the moment the answer is flattened.

How is the answer flattened, and how is it known that all of it is there?

Flattening is the plainest operation of the lot. The procedure walks the eight stall objects, and inside each one walks its list of month objects, writing one row for every month object with the stall details copied down onto it. Eight walks over the outer level, 32 walks over the inner level, 32 rows written. Done on the Neelbagh answer, out come 32 rows across 8 columns. Of those, 31 takings cells carry a number and total Rs 21,40,099/-, and under the named convention that total reads Rs 69,035.45/-. The 32 rows are the file, row for row and rupee for rupee.

# walk the objects and write one row for every month object
rows = []
for stall in answer["stalls"]:
    for m in stall["months"]:
        rows.append({
            "stall_id":       stall["stall_id"],
            "stall_name":     stall["stall_name"],
            "month":          m["month"],
            "takings_rupees": m["takings_rupees"],
            "filed_on_day":   m["filed_on_day"],
        })

# the check that makes it safe: what was sent, against what was written
sent = sum(len(s["months"]) for s in answer["stalls"])
print(sent, len(rows))          # 32 32

Flattening the eight stall objects into rows, and the two line count that proves nothing was lost on the way.

The last two lines are the entire safety of the operation. Count the month objects that arrived, count the rows that were written, and say both numbers out loud in the same breath. If they agree, the flattening was faithful. If the second is smaller, something was skipped, and the commonest cause is a stall object whose month list was empty and quietly contributed nothing. If the second is larger, something was written twice. A flattening that reports no count has said nothing about whether it worked, and no error message will arrive to fill the gap.

Ten shops in one shopping centre each hand the manager a folder of monthly slips, and somebody types the whole lot into one sheet. Nobody in that building can tell by looking whether the sheet is complete. The only honest check is to count the slips in the folders and count the lines in the sheet, and the two numbers either agree or they do not. Flattening an answer is the same act with the folders replaced by objects.

What is paging, and what does a read that stops early cost?

Whoever publishes an address decides how much of a record one answer may carry, and that decision is called the delivery size. The delivery size has nothing to do with the analyst and nothing to do with the record. At ten rows a delivery the Neelbagh record arrives as four deliveries of 10, 10, 10 and 2 rows, and every one of those deliveries is a complete, well formed, entirely correct answer to the request that produced it.

Read alone, the four deliveries give figures that have almost nothing in common. Delivery one reads Rs 38,500/-. Delivery two reads Rs 34,900/-. Only nine of its ten rows carry a usable figure, the empty takings box of Bansi Flour being the tenth. Both of the rows reading Rs 4,80,000/- and the market office code of 99999 land on delivery three, so delivery three reads Rs 1,35,199.90/-. Delivery four, holding two rows, reads Rs 44,500/-.

Now read the last column downwards. The last column is the one that matters, and it does not behave the way people expect. The running reading starts at Rs 38,500/-, falls to Rs 36,794.74/-, jumps to Rs 70,727.55/- and settles at Rs 69,035.45/-. The running reading goes below the whole record reading, then above it, then back to it. The running reading crosses the whole record reading exactly once, and never sits on that reading until the last delivery has arrived.

The direction of a short read is the easiest thing to get wrong. On this record a read that stops at delivery one reads low, and it is tempting to turn that into a rule. Reading low is not a rule. Reading low is a property of the row orderThe order rows happen to sit in when they are handed over. Row order is a fact about the delivery, not about the record, and reordering the same rows changes it without changing a single figure. the market office typed, and nothing more. Shuffle the same 32 rows and the running reading takes an entirely different path to the same destination. A short read is not a low read; it is a correct answer to a question nobody asked.

What does an address refuse to say?

Three things never arrive with the answer, and no field in the shape carries any of them.

The first is when the record behind the address last changed. An answer is a picture of the record at the moment it was written, and nothing in it dates that moment. The second is whether any row already held has since been revised. Harit Greens filed month 2 twice, on day 5 and again on day 19, and the second filing withdrew the first. A read taken before day 19 would hold a figure that has since been taken back, and reading again would not identify which rows had changed unless they were compared directly. The third is what the market office does when a return never arrives. The Chandan Tea row for month 4 reads 99999, a perfectly good whole number and in fact the office code meaning no return was received. Nothing in the shape says so. The field is present, the type is right, and the figure is not money.

All three of these belong to provenanceThe paper trail behind a number: which record it was lifted out of, and every hand that altered it before it was seen. Nobody writes it down and it is simply gone., and provenance is not a field in the answer. Provenance has to be asked for separately, in words, from whoever publishes the address, and if nobody can say then the honest thing is to record that nobody could. An answer that is silent about revisions is not an answer that says there were none.

What is written down the first time a record is read from an address?

An analyst, a lender or a small business operator reading their own point of sale figures all face the same moment: the first successful read. The note written down in that moment decides whether the figure is defensible six months later, when nobody remembers what was asked. Five things, and every one is a fact rather than a judgement.

1. The exact path, and everything written into it. Not the description of the request, the request. A delivery size changed on the far side changes what a repeat of the same request returns, so somebody re-running it later needs the characters, including the delivery size.
2. The grain of the answer, in one sentence. One month object describes one stall in one month. A grain nobody wrote down is a grain that changes quietly, so write it as a sentence.
3. How many objects came back and how many rows they flatten to. Eight and 32. Two numbers, one line, and it is the only evidence that the flattening was faithful.
4. The delivery size, and how many deliveries were actually read. Ten rows, four deliveries read out of four. The delivery count is the line that would have caught the failure below, and it is the line most often left out.
5. Every code and special value the answer uses. 99999 for a return that never arrived. An explicitly empty value for a return filed with the box left blank. The codes and the empty values decide the denominatorWhatever the total was divided by. Two people can divide one identical total by two different counts, both be right about the arithmetic, and disagree entirely about what the result describes., so they are not a detail.

A figure computed from an answer that arrived in deliveries, without the delivery count printed beside it, is not slightly incomplete, it is unattributable. Nobody afterwards can tell whether the figure describes the record or a tenth of it. The claim is harder than it sounds. Such a figure cannot be defended, cannot be corrected, and cannot even be shown to be wrong without going back to the address and reading the whole record again.