#1 Tip a Day: Prevent Excel Automatically Creating Hyperlinks

#1 Tip a Day: Prevent Excel Automatically Creating Hyperlinks

This tip is # 1 in a series of a Tip a Day for December 2015. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

Does it annoy you when Excel automatically creates hyperlinks to anything that looks like a web address? When you first hit enter on the cell after inputting the address, you can press Ctrl+z to undo the insertion of the hyperlink. However, if you’re after more than a piecemeal approach, and want Excel to stop doing it all together, here’s how you do it.

  1. Go to File -> Options -> Proofing
  2. Click the “AutoCorrect Options…” button
  3. Go to the “AutoFormat As You Type” tab
  4. Untick the top radio box: “Internet and network paths with hyperlinks

Note, this tip also works with Word!

An Excel tip a day in December

December is a quiet time, typically. So I have decided to have a crack at putting up an Excel tip each business day in December. They will range from uncomplicated tips useful to everyone through to much more complex tips that you may never have a need for. But hopefully, there will be something in there for everyone.

Let’s finish 2015 off with some momentum to make sure that 2016 is super-productive!

Note, the tips will be for Excel 2007+ on Windows.

Keep your eye out on December 1!

Excel System to send many customised emails

Excel System to send many customised emails

I, like most people these days, receive a truck load of emails. Many come from banks and are mostly addressed to “Hi”. There are plenty of posts on the internet about how to get engagement with your emails and increase open rates. Apparently, but unsurprisingly, making the recipient feel as though you have sent the email to them alone (i.e. personalising the email) is a great way to increase engagement.

Recently, I participated in the Cancer Council’s Relay For Life. Our team joined a little late, and I was travelling and under a bit of pressure at work which meant that leading into the final week the amount of money I raised was paltry. In fact, I had “raised” a total of $100. When I say “raised”, I am being a little disingenuous – I was the one who made the donation!

So I decided to take the time-poor approach to raising funds. Spam my LinkedIn contacts. I rarely ask anyone for anything (except James Waddell), so I thought it would be ok for me to hit them all up for a donation. At best I would raise a bit of money and potentially reconnect with a bunch of people who I hadn’t spoken to in a while. At worst, I would piss some people off for a good cause. If you receive an individualised email it’s not spam, surely?

A few years ago I wrote a VBA program for a friend at UBS to enable him to email his growing list of clients with a personalised greeting. As an avid reader of his excellent overnight recap, I wanted the email to be addressed to me rather than “Happy Friday”. So I dusted off the old spreadsheet, updated the code to enable it to insert the signature, dropped in my LinkedIn contacts and Boom! Individualised emails addressed to my LinkedIn contact list. Very efficient.

So, how did this approach go? Well, in 1 week my fundraising total rose from $100 to $2,500. Not bad! Hitting the “Go” button on my LinkedIn list is literally all I did. I also managed to reconnect with a bunch of old colleagues and acquaintances, so it was a double win!

If you’d like a copy of the file, just sign up on the popup, or contact me. As always, please use it for good and not evil.


Cross Currency Swap execution

TL;DR: CCIRS execution template

Executing bonds offshore is exciting, and often intense. There are often huge volumes of work to get through (on top of normal duties). Documentation issues, negotiations, presentations, Board papers, travel… so much going on. All too often, this makes the cross currency swap a comparative afterthought in senior people’s minds.Unfortunately, that often pushes the swap’s execution to the back of the bus, sometimes with expensive consequences. The bond is all about the margin to Treasuries, right?

T+Margin is important, but there is a lot more that happens before you end up with what you want – floating BBSW. Obsessing over the margin to Treasuries while leaking 5 points on the swap is economically irrational.

So how can you ensure an appropriate result is achieved in the cross currency swap? My tips for doing so are as follows:

  1. Don’t just throw the swap execution/management to the least capable person on your team. On the bank’s side, they certainly won’t. Note, the least capable <> the most junior!
  2. Know the swap. Nail down all of the negotiable parts prior to execution. Get screen shots for the rest. Use the spreadsheet below to help you. The bank dealers don’t really like this type of approach, and it will cause arguments. Often heated arguments. Without arguments, you’re not trying hard enough! Don’t worry, they’ll still make a small fortune and will soon forget that argument.
  3. Take more than one bank to execution. This is very important. You need to be able to drop banks if they try to play funny.

Sam He, a former colleague of mine, created the spreadsheet below. It is brilliant, the most detailed CCIRS execution template I’ve seen. I’ve never seen a text book go into anything near this level of detail. I highly recommend you use it in your cross currency swap execution process – it will lead to a better result. The spreadsheet works with both Reuters and Bloomberg, and is for AUD/USD CCIRS. If you’re looking at executing a different CCIRS, say AUD/EUR, let me know or drop Sam a line. I’m sure he’d love to help out.

CCIRS execution template

Note: The macros only copy the live pricing across, disabling them will not affect the operation of spreadsheet.

Programming: Modern day Bob The Builder

Programming: Modern day Bob The Builder

The AFR released a story today entitled, “‘Learn to code’ and 8 other career tips from economists“. (If you don’t have an AFR subscription, you can bypass the paywall with my extension here). Whilst quite US-centric, and the coding advice was a bit thin, I am inclined to agree with the sentiment of the title.

I’ve always advocated to young people working within financial services the benefits of learning some programming. Some get it, some don’t.

I believe that corporate Australia would be well served to encourage more of a hacking culture. Where people didn’t wait for someone to fix their problems, they fixed them themselves. Where problems are not solved by buying in expertise or solutions. Where people strive to do things a little better everyday. Faster, more predictable. I am not talking about big problems, but small ones we face every day. Incremental gains are generally the key to long-term success.

For example, I once created an FX-position keeping system that enabled real-time FX positions by drawing data from our Treasury system in real-time. The spreadsheet provided the trader (me) with live information on positions without relying on sales people to call them out (they’re unreliable). Additionally, the closing process was reduced from up to 45 minutes, as you had to try to piece together all the mystery positions you hadn’t been told about, to practically nil. Did I have to do it? No, the old paper-based system was in use for 10+ years before I arrived. Given it is still in use today, some 8 years later, I guess it was a useful exercise. Certainly, the ROI will have been phenomenal (although sometimes I would rather not have known what my positions were!).

In the future, those who can program are definitely going to be better placed to succeed in the workplace. So if you want to work in a numerate profession, I suggest you take some programming classes. You don’t need to be a professional programmer, I’m certainly nowhere near it. But just knowing the fundamentals and what is possible is a great addition to your toolkit as an employee (or entrepreneur).


3 Tips from a Lotto insider

The lottery is extremely popular in Australia. In fact, it is estimated that in the last 6 months in excess of half of the adult population have participated. Working for Tatts has definitely shaped my view on how best to play the lottery. Whilst the odds are very low of actually landing Division 1, I can assure you that people do it every single week.

Firstly, for those who haven’t asked me yet (not many people left): I have no idea what the numbers are going to be!

My lottery philosophy centres on the belief that lightning doesn’t strike twice, so making it count if you do win is important. So, here are my top 3 tips for ensuring that if you are lucky enough to win Division 1, it counts.

  1. Know your games, and what you will win. You don’t want to be disappointed when lightning strikes. Saturday Lotto will win you between $500k and $2M, because you’re going to share Div 1. Ozlotto and Powerball, meanwhile, are more likely to make you a sole winner. It would be very unusual to share an Oz or Powerball win with more than one other winner.
  2. Have a number over 31 in every game you play. Every single one. People are creatures of habit and superstition. They play birthdays, a lot. Lottery numbers go up to 45 –  but no one was born on the 37th of January, were they? Sharing is not caring when you win the lottery, it could literally cost you millions.
  3. Use auto-subscribe on the app. Don’t find out after the draw that your numbers would have won! This is a really useful function on the app – you can set the system to only buy a ticket when the Division 1 is large enough. Wouldn’t get out of bed for less that $10M? No problem, you can automatically purchase a ticket when it gets to that level.

Ok, there’s a couple of tips for ensuring that if your numbers come up, you’re making every post a winner. Chances are you’ll never win it, but… Wouldn’t it be nice?