Million Dollar Traders, Nickel & Dime EDI

They don't seem to do "Documentaries" any more. It is all "Reality TV" these days, and I have mostly had enough of them. I wish shows like "The Apprentice" would show more details of the problem solving, less of the problem personalities. But such is the rarity of Business related material I gave this new show a go.

Million Dollar Traders - the premise: take some ordinary people who show some promising aptitude, train them intensively for a short period, then let them loose with real money and see how then perform as a Hedge Fund.

Unfortunately I didn't learn much about the workings of The City, Finance and Hedge Funds. I learnt more than I wanted to about ordinary people under stress.

What caught my eye was the technology on display. Each trader had 2 or 3 computer monitors showing large amounts of numbers and graphs. Real time data was so important it was considered a major faux pas not to be at the desk the moment the markets opened. They were constantly crunching numbers with calculators as they balanced (or hedged) risk trying to find a profit margin. All this was happening as the Credit Crunch Tsunami hit, and banks crumbled into chaos. After 3 weeks I think the best trader made about 1% gain, compared with an industry average of 5% loss. My rough guess is that this represents £2,000.

Throughout this time we watched as the trainee traders weighed their investment decisions, the tension and drama was built up. We sat on the edge of our seats as they finally committed themselves to make a deal....

...and then they picked up the phone...

Uh?

No "Accept" button to click. No https protocol session. But a conversation with a real person. A request to buy or sell (go long or short in the jargon). A query on the price and a confirmation on the total cost. They then got out of their chair with a piece of paper (presumably some sort of record of the deal), walked across the room and got it stamped in some sort of machine that made a satisfying "kerching" sound.

At first I put this down to artistic license and the program makers wanting to make more interesting imagery. Like in the film Independence Day where, they bring down an alien war machine with a software virus. As unlikely as it is that the aliens have no anti-virus software, they leave out the incredibly quick development of the USB port to NBSA port adapter hardware (Never Before Seen Alien port).

Then in the last episode a strange thing happened. The boss was upset. He was a REAL trader. He knew how it was. The new guys were not coming up to scratch. He didn't like their attitude. It was all wrong. So they started discussing acceptable telephone manners when on the phone to a broker. At issue was the fact that some of the traders were spending too much time saying "hello", "please" and "thank you". He insisted that the Brokers will be laughing at them the minute the phone was put down. If the traders were not short and blunt they would get no respect. The brokers would be less likely to do their job as efficiently.

My jaw dropped. It was for real. The boss was prepared to openly admit a willingness to trample all in his way in the focused pursuit of money. I understood that. It was in the nature of the job. Yet he was prepared to rely on a fallible human, being able to interpret verbal instructions communicated by another fallible human. Indeed, in the first episode one guy accidentally got his "buy" and "sell" mixed up. In the 20 seconds it took to correct his mistake, he lost money.

The labour cost of those transactions was just silly. Yet the volume of trading, city wide, is huge. I know the term "EDI" is not a trendy buzzword, but where was the - Electronic - Data - Interchange?

I know the world of Finance has taken a heavy PR knock recently. I have heard the view expressed elsewhere that those in charge didn't really understand what was going on. But so long as the money kept rolling, questionable practices remained under the radar. If this TV program is representative then to me it would seem to confirm this view.

Here is an important life lesson I learnt as a small child watching The Wizard of Oz.

Despite what the Wizard says, don't ignore the man behind the curtain. His presence is telling you something!

Alternative EDI Formats Part II – JSON & Protocol Buffers

In the previous post I wrote how a large amount of EDI (that is Electronic Data Interchangein the widest sense) is done, without using a strict formalised standard, using CSV formats. Now Google has released details of how they execute server-to-server/program-to-program message interchange using Protocol Buffers. You won’t see the term EDI any where on Google but then the term doesn’t have a sexy web 2.0 image.

Google rejected the use of XML. I am all for that. To be fair, I think this is more to do with the desire for a binary format for super fast, supper scalable encoding and decoding. Inter-company EDI is universally text based. I can’t see that changing.

The first thing I noticed about the .proto files is their similarity to JSON. Their use seems to have pre-dated the popularisation of JSON. In other areas I have seen Google use YAML for similar definition purposes.

The .proto files are not message files. They are not sent as part of a message, ever. They are used to automatically compile programs to handle messages in the format defined by these files.

Now this struck me, because I think this is one area where CSV beats traditional EDI standards. That first row, of column headings, is like the file definition. If a trading partner adds new columns (or removes columns, or moves columns) the next time he sends the same type of message, it doesn’t matter. We don’t need to agree beforehand. The reciever can identify which cell is which piece of information by locating the column heading position.

Stripping the .proto example down to equate it with our first simplified JSON message data from the previous post, we get the following,

[[‘Jodie Foster’,1,’jfoster@silence.com’,’555-1234’],
[‘Sigourney Weaver’,2,’sweaver@alien.org’,’555-9876’],
[‘Drew Barrymore’,3,’dbarrymore@angel.net’,’555-2468’]]

message Person {
required string name = 1;
required int32 id = 2;
optional string email = 3;
repeated PhoneNumber phone = 4;
}

The field list is in a [Modifier – Type – Field Name – Sequence] format. Modifier and Type wouldn’t make much sense in JSON which is not restrictive in its type usage. Incorporating the sequence number into our JSON definition section gives us a useful ability.

{‘definition’:{‘name’:0,’id’:1,’email’:2}}

MessageObject.definition[‘name’] returns 0
Or,
MessageObject.definition.name returns 0

MessageObject.data[0][MessageObject.definition[‘name’]] returns Jodie Foster

Now we have the same ability to cope with our trading partners adding, moving and removing fields without the format losing its meaning.

<aside> Did you notice Google started numbering at 1 and not 0? What is that about? That is Muggle thinking! </aside>

What happens when we expand the pone field into a sub-table like before? On its own this sub-table would have a definition of,

{‘phonenumber’:0,’type’:1}

but we can't just slot this in and replace the existing phone field definition becuase we would lose the positional data. What Protocol Buffers does is list the definitions separately.

{‘definition’:{‘person’:{‘name’:0,’id’:1,’email’:2,’phone’:3},
'phone':{’phonenumber’:0,'type':1}},
‘data’:[[‘Jodie Foster’,1,’jfoster@silence.com’,
[[’555-1234’,'home'],
['555-777','mobile'],
['555-1235','fax']]],
[‘Sigourney Weaver’,2,’sweaver@alien.org’,
[[’555-9876’,'home'],
['555-0101','office']]],
[‘Drew Barrymore’,3,’dbarrymore@angel.net’,
[[’555-2468’,'home']]]]}

a=MessageObject.definition['person']['phone']
b=MessageObject.definition['phone']['phonenumber']
c=MessageObject.definition['phone']['phonetype']
MessageObject.data[0][a][2][b] returns 555-1235
MessageObject.data[0][a][2][c] returns fax

In this way the sender can omit any fields they like and the field sequence is no longer important. The receiver can still parse the message and extract the data segments. The message file size is kept to a minimum. returns

This is not JSONML (althogh that is intresting in it's own right) . This is about efficiently transporting a (potentially large) list of data objects of the same type.

EDI System Design - Overview

Part I - Message Control
Part II - Export Mapping
Part III - Import Mapping
Part IV - Transaction Processing

EDI System Design Part IV - Transaction Processing

I have mentioned before that this is the "Elephant in the room" no one likes to talk about. It is hard to talk in abstract terms about something so involved so I am going to take a specific example and use that of a customer sending their Purchase Order. We would want to turn this into a Sales Order.

So we get hold of a copy of the message format definition (Edifact, X12, Tradacom etc.), blow off the dust and start to study it. It might have, say 100 fields - Gulp! Then we look at our Sales Order database tables. These might have 500 fields - Double Gulp! How can we possibly map or translate from one to the other? The answer is we don't, you can't possibly. We have to stop thinking in terms of messages and documents, and think in terms of process.

When our company recieves an order in the mail or by fax, it gets typed into our ERP system. The user does not typically type hundreds of fields. The system should be optimised for speedy entry. A quick list of things that might be entered,

customer account, purchase order reference, delivery address, product code, product quantity, unit of measure, price, delivery date... eh... I've started to run out already... that is only 8.

Then why all those fields on the database tables? Well they do get updated, but they don't all get entered. For example, one of the fields might be Payment Terms, but we don't re-enter it on every order. It gets defaulted. Probably to a value stored on the customer database table. We might have the option of amending it when we type in an order but we lose that option for EDI (Electronic Data Interchange) orders. It doesn't mean it can't be amended sometime after the order was raised. It doesn't mean we can't introduce a rule to flex the setting based on various parameters. The point is we have to remove the human element and we certainly aren't going to let a customer enter the Payment Terms.

We do the analysis of what fields are actually entered. The Import Mapping routine finds where they are in the EDI message, and has stored them in an "pending" database table that looks like a very trimmed down version of the sales order table.

There are some fields that the customer might find problematic to send. Typically our codes for things like unit of measure. We might use EACH, PACK, M, KG. They might use ONE, BOX, MTR, KGS. This is where a universal standard would be useful, but there is no such thing. So we have to set up look-up tables to translate the standards codes or customers codes to ours. Codes are a large subject, but in order not to get bogged down I will just mention that the subject includes, among others, customers branch codes and product codes.

Our processing function will take each input in turn and apply the same field defaulting and validations as our order input screen. If any field fails input validation, the order must be abandoned. At the end we must perform the same update process.

To do this we don't want to re-write the whole order input process. We want to re-use the existing code. We have to hope we have an object-oriented system with methods to call, or structured programming with functions, or at the very least re-useable subroutines. If we have spaghetti code, or no access to source code at all, then we are in trouble.

Any way, when this routine has finished processing an order (wether it was sucessfull or not), we move the "pending" database object to a "processed" or "archived" table. We add any successfull sales order ids or unsucessfull error messages (such as "Order XXXX, Line XXXX : Failed to validate item code XXXX"), so we can produce an audit report. We might go on to develop an edit screen to amend failed order messages and allow them to be unarchieved and processed again.

Most important of all is the entry of price and delivery date. If our existing system allows free entry of these fields, then we need to tighten up it up. We can't allow the customer to enter whatever value they like and anyway, some customers might not send any price or date. We certainly aren't going to send it to them for free! What is a good idea, is to accept any values higher than our system default. If it is less, replace it with our minimum and add a message to the audit trail like "Order XXXX, Line XXXX : Price 11.11 requested, 99.99 used".

One final idea. If the system doesn't already check the customers purchase order reference to make sure that the order isn't a repeat that we have already processed, then we need to add it. I have had batches of hundreds of messages sent twice. I have had customers who insist on sending hard copies in the post and the sales order entry people enter them a second time!

EDI System Design Part I - Message Control

Assuming you have an ERP system that processes sales orders, invoices, remittances, stock balances etc. how do add EDI (Electronic Data Interchange) capabilities? The first thing we need is to decide what is going to whom, in what format, by what means.

Let us suppose we want to start sending Sales Invoices to a key customer. The routine that will create the EDI invoice could be called from the ERP routine that does the invoicing, or it could be called from a periodic routine run sometime after. We will need a EDI Sales Invoice Control screen.
Customer:
Message Format:
Protocol:

Suppose we want to start sending Order Acknowledgements. We would have to create a similar screen. To avoid this, we add a Document field. Suppose the standards body releases a latest version of the message format. Some of our partners start to use it. Some stick with the old version. We could add a Format Version field or incorporate the version in the format.
Document:
Customer:
Message Format:
Message Format Version:
Protocol:

Some customers will have multiple branches or delivery points. I warn you from experience, there is a high probability that customer A only has half their branches EDI enabled, while customer B will have all branches enabled. So customer can't be the group account and must be the branch account.

Now a key supplier want us to send our purchase orders, so we add a Supplier field. One of, Customer or Supplier, must be entered. Next the taxman wants our sales tax information. Is he a customer or a supplier? Neither really. With luck you will have a Contacts file that incorporates all these entities and you can use that identity. Otherwise we need a Contact Type field (answer customer/supplier/third party) and Contact ID.
Document:
Contact Type:
Contact ID:
Message Format:
Message Format Version:
Protocol:

Some Document, Format and Protocol combinations will be compatible with immediate delivery e.g. sending purchase orders by email PDF attachment. However some formats are designed for batching up several messages into one. Also a partner might want a steady trickle of messages through the day and prefer one combined message say, once a day or once a week. If you are communicating by Dial-up (facsimile or point-to-point) there will be savings if messages can be combined. To achieve this we need to add a Batched Y/N option. If 'Yes' then the document ID is placed in a pending file to be translated and communicated at a later time.

At last we come to Protocol. We can't just store FTP, HTTP, SMTP, P2P etc. in each case we require more information e.g. URL, user name, password, phone number, alternative phone number. When you have a partner with lots of branches but central communication, this would mean a lot of repeated set up. It would also make batching very difficult because we would have to check every field.

The solution is another level of abstraction. We create a concept of Communication Type and this points to another screen where the additional fields are stored. Then, when Acme Widgets have all their stores documents going through one VAN mailbox, we only have to store the Communication Type identity.

So finally we have...
Document:
Contact Type:
Contact ID:
Message Format:
Message Format Version:
Batched? Y/N:
Communication Type:

Update - I was trying to keep this post as short as possible, but it just keeps on growing. I realised I missed another field. There some other pieces of information that you might need. Examples are where a customer wants EDI invoices but only for orders raised by EDI. Or the customer prohibits amendments to EDI sourced orders. Or the Trading Partner requests special formatting of a reference used in the message. There could be a need for several flags for minor functionality. Rather than clutter this screen with more fields, I tend to add a generic "Options" list on a sub screen.

Part II

Overview Part III - Why EDI? (or, What to do with EDI Messages)

How is all this beneficial? How is it better than faxing or emailing a word attachment? In a human sense it isn't. The EDI (Electronic Data Interchange) formats are often not human readable (e.g. Edifact, Tradacom). The point is, it is meant to be machine readable.

Eh? So what? These days we have grown used to unstructured information but it wasn't always the way. The triumph of HTML (and the Internet) is the organising of unstructured information. This brings us on the often over-looked EDI 5th element. The elephant in the room no-one talks about. I left it off the list in Part I because I didn't want to scare you.

So what most people were, and are, doing was putting these messages into an database. For most people this means an RDBMS. For most this means SQL. It is a relatively easy task to design a database schema for a particular type of message (e.g. stock balance, planned delivery etc.) in a particular format (e.g. Edifact, Tradacom). But remember you will have many customers using their own choice of format and their own interpretation of the standard. They all need to go into the same schema.

Other EDI guides will tell you EDI is all about accuracy. Reducing typing errors. This is true. You will now have in your RDBMS an accurate representation of the message. So what are you going to do with it? Report it? Print it out? Change it from electronic data to hard copy? You will be surprised how common this is because if you do, as far as your customer (eh... I mean trading partner) is concerned, you will appear to be "EDI Enabled". You will have ticked the boxes.

EDI becomes really useful when the message is a transaction. If your customer sends you a purchase order and you can create a sales order on your business computer system (sales order / ERP system), without any typing, then there are opportunities for labour cost reduction. So EDI is very popular in high volume situations, like supermarket supply chain.

Unfortunately a transaction is more than just a message or a schema. What if the customer orders a product you don't recognise? Requests a delivery date that doesn't respect your quoted lead time? Asks for a ridiculous price? Each and every field has to be rigorously validated and you have to have a plan to cope when it doesn't validate. After all, who turns down a customer order? Equally you don't want to all that Call Centre labour saving to be swallowed up in increased IT admin costs.

So if you are a small fish supplying a big shark, it is possible you decide to find the 80/20 split by turning all this complicated EDI into a glorified fax machine. It happens.

Part I, Part II