Last Friday I posed a question about how one would derive returns when they had limited data: month-end holdings and period transactions. A couple folks basically explained that this CAN be done. Here’s what you do:

• Take your holdings file and preceding transactions and
• back into the holdings for any prior period.

By transactions, we’re assuming you have buys, sells, income, and corporate actions. With this information, you can get anywhere.

Really simple example: your holdings file for 12/31/09 shows you have 100 shares of IBM and 200 shares of Ford, plus $500 in cash. You’d like to know what you looked like on 11/30/09. Your 11/30/09 – 12/31/09 transactions list:

• 12/15/09: sale of 1,000 shares of Citicorp for $5,000
• 12/17/09: purchase 200 shares of Ford for $3,000
• 12/23/09: withdrawal of $1,500

What we do is start with our current position and reverse all the entries:

• 12/23/09: withdrawal of $1,500 becomes a contribution of $1,500 (our $500 becomes $2,000)
• 12/17/09: purchase of 200 shares of Ford for $3,000 becomes a sale of 200 shares (our 200 shares go to zero and we add $3000 to cash)
• 12/15/09: sale of 1,000 Citi becomes a purchase of 1,000 shares (we pick up 1,000 shares and reduce our cash by $5,000)

Meaning, that on 11/30/09 we held:

• 1,000 shares of Citi
• 100 shares of IBM
• zero cash.

This may help:

As you can see, I took our starting position and simply reversed all the entries. Granted, this was a very simple example, but you at least see how it works. You would need to get pricing for 11/30/09.

By the way, if you plan to do this manually, depending on how many periods you need to create and how many positions and transactions, you could be doing it a while.

What’s the lesson here? Well, first, it means you do not have to store every day’s position! You can store monthly positions and always back into any specific day. In the world of IT (information technology), there’s always the issue about processing time versus space. Granted, disc storage has gotten a lot cheaper, but if you don’t need to store dailies, why bother? Second, if you have to reconstruct records and have minimal information, you may still be able to accomplish it.