Back in May I wrote about joining the Twitter bandwagon to see whether or not this new social media outlet had any relevance to my daily responsibilities.  After about 400 updates in my Twitter profile, I struck gold.

Over the last week, our firm has been implementing tax loss harvesting strategies across our entire book of assets.  As one might imagine, there are many, many individual trades to identify, submit, track, and confirm, as we want to ensure that all activity is executed without errors for our clients.

The Trade Log

Part of our process includes the update of a trade log so that it contains details of all our trading activities.  We use several custodians for client assets, so to date we have consolidated all activity into one Excel worksheet.  Therefore, all trading data is in one place and can be sorted, screened, and filtered according to desired data.

Each trade is listed on one row in our trade log.  When we execute a swap, the sell trade appears on row 1 and the buy trade appears below on row 2.  That simplifies the process of watching for wash sales, as I can sort all the sells by date and group them together so we can quickly see what funds we shouldn’t rebuy unless the 30-day repurchase window passes. 

Here’s an approximation of what the trade log looks like (account numbers and other data changed to protect the innocent). 

Trades are listed one per row

Pretty straightforward stuff.

Custodian Trade Format

A portion of our clients’ assets are custodied by a 3rd party money manager and we have been trading many securities through their bulk request system.  This custodian wants bulk trade instructions submitted on a spreadsheet (rather than uploads that Schwab, Pershing, and others support, but that’s because they’re not 3rd party money managers!).  That’s not a problem, except that they want the sell and buy trades for the security swap listed on the same row.  Ok, I can do that.

Buy and sell trades appear in the same row

But the problem I have is when I want to migrate the buy and sell trades from the sheet formatted for the custodian to the sheet formatted for the trade log.  How do I interlace or interweave the buy and sell instructions so each has its own row?

Twitter to the Rescue!

Enter Twitter.  On the 15th, I posted the following tweet:

Excel help! Column A has 1234etc, Column B has ABCDetc. I want Column C to contain 1A2B3C4D etc. How do I interweave 2 columns?

After one false start, I received the answer that I was looking for from Debra Dalgleish of Contextures.com and an Excel MVP, within one day:

@BillWinterberg For results in col C, enter this in C1 copy down: =OFFSET($A$1,ROW()/2+IF(MOD(ROW(),2)=1,0,-1),IF(MOD(ROW(),2)=1,0,1)) 

I can use this formula to take the data formatted in the custodian spreadsheet and convert it so each sell (pasted into Column A) and buy transaction (pasted into Column B) are on its own row in Column C.  Try it out for yourself!

I came up empty after many Google searches for the solution, so I’m really impressed how this Twitter experiment turned out.

Not using Twitter?

Go to their website, click “Join,” choose a username (I recommend something similar to your real name and not “CoolFinanceGrrl”) and start tweeting.  I also recommend TweetDeck to consolidate your tweets and simplify the management of people you follow.

Related posts:

• Boarding the Twitter Bandwagon
• Get the Buzz on Twitscoop
• NAPFA Technology Conference Report: Day Two

Enjoy FPPad.com? Get FPPad updates via RSS or get updates by Email

This entry was posted on Friday, December 19th, 2008 at 2:00 pm and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.