Productivity Tips, MS Excel and Inspiration

image: varchev.com

I am currently doing a Master of Science in Financial Engineering (MScFE) with the WorldQuant University (currently they operate tuition free, you can apply via https://wqu.org/) I am in my third course out of total of 12; running into my fifth month out of 18 total months for the MSc.

It has been a very pleasant experience. I found it more structured and rigorous than the non-free MBA I did with a European university. So, if you are interested in the world of quantitative finance and investments, I would say you should give it a try. Just know that getting in is not a walk in the park and the program is very demanding -- you'll do lots of advanced calculus, some python and R programming, some finance and lots of mathematics.

Last year, I finally got inducted as a chartered stockbroker upon completing the intensive 1 year exams. And due to a pact CIS Nigeria has with UK Chartered Institute of Securities and Investments, I was able to do just one ethics-type exam and become a member of the UK Chartered Institute of Securities and Investments. So I now use the designations ACS and ACISI (UK) in front of my name. And more importantly, I have achieved one of the milestones to my goal of setting up a financial securities trading and investment company.

I hope to use the MScFE to learn the skill of building financial investment products and improving my securities analysis skills.

I have been taking a more structured approach to my investing. I now do a monthly allocation of my ex-expenses disposable income to different asset classes:

20% to Real Estate
50% to Stocks
10% to Money Market
20% to Cryptocurrencies

In addition, all my online USD income goes to my US equities account.

The needed push to taking this structured approach to having a portfolio that spans these major asset classes came from the learning in the MScFE course 1 (Financial Markets). So no more thinking asset class A vs asset class B, but how much of asset class A and how much of asset class B.

This year I hope to see more improvement in my personal finance and investments as I learn more from the program and put those learning to practice.

If you have Telegram, then you should try out the chatbot I created for the Nigerian stocks and macro-economic data: t.me/NigeriaMarketDataBot

You can ask it for the stock price of any listed company or bond. You can ask what the parallel exchange rate is. You can ask it what the current level of Nigeria's FX reserve is (clue, it is at a very worrying level, start insulating yourself against a likely devaluation). You can ask it what the financial statements of Zenith bank (or another listed company) are. And you'll get answers right in your Telegram.

Awesome, isn't it?

You can also access it on Facebook Messenger: https://www.messenger.com/t/nigeriamarketdata

And also,

wait for it

wait for it

*drum rolls*

*more drum rolls*

I have figured out a way to do very well in the Nigerian stocks market. And if you consider my pedigree - 14 years in the market with the last 5 years being one of intense active investing/trading on the NSE, and that I have been building solutions + analysis around the stock market -- then you too can feel as optimistic about what I am about to announce just as much as I felt when I came about the discovery.

Here's the background story.

In 2006, I bought my first shares. In less than a year it grew x10.
In 2009, I spent a huge chunk of my savings in Fin Bank IPO. I didn't get shares certificate and all my efforts to trace were futile. I swore off IPOs in Nigeria. It was like sending a parcel via NIPOST. If anything goes wrong, you are on your own, the system was messed up will little accountability and you'll just be bounced around. (Anyway, I heard its positively different now).
In 2011, I started playing the secondary market.
In 2012, I started stockpiling and analysing financial statements of companies listed on the NSE.
In 2013, I created the most accessible online repository of those financial statements for downloading by anyone online. For free.
In 2014, I started putting approximately all my savings in the stock market.
From 2015 - 2019, I was high, I was low and I was mostly confused. All the book knowledge, analytical work and patience were not paying off as I expected. And no one could explain in an eye opening way what to do differently. Believe me, I did ask a lot of questions from people I thought should know. Most sounded like they've given up on the Nigerian stock market. One even is the head of a top investment management firm. Oh, I asked the guys at NSE too.
~~2020~~ Today: I think I have found the strategy I have been lacking.

You can access my discovery at http://vip.urbizedge.com/economy

I found out that using the MACD of 50 SMA ~ 250 SMA, I would be able to get longterm buy and sell signals. I wouldn't be day trading or doing swing trading or any of the short term trading I am not interested in (as I prefer long term investing and want to avoid the crazy high transaction charges in our market). Backtesting this MACD based strategy has been a reconfirming outcome. So much that I feel bad for not coming upon this earlier.

Now if all I have said sounds like Greek to you. A simpler equivalent is that I have found a way to know when the stock market (or specific company) is going down seriously and when it's going up seriously.

When you visit the automated Power BI dashboard I use to track these, http://vip.urbizedge.com/economy , the last two pages are the critical ones. And you too can freely use this.

Cheers!

image: inspiringwishes.com

Happy new year! Welcome to year 2020! May it be a glorious year for you and all your loved ones.

My new year resolution is to write a blog post daily from 1st of January 2020 to 31st of December 2020. That is my only special resolution this year.

Last night, I did a quick review of 2019 and what to improve in the new year. My company, UrBizEdge, grew greatly in 2019 but it's becoming extremely demanding on me. I am constantly cycling from worries of salaries and expenses to onslaught of competition to owing clients to deadlines on current projects to employee management issues to product development to customer demands to emails/chats/meetings/requests overload. I can't seem to have a mentally relaxed day or even hour.

Right now I am struggling to set aside a new concern about how two of my staff are no longer pulling their weights and just doing the barest minimum plus a bit of eye service.

I have not been able to continue my novel, Akin Smith. I no longer do my French learning exercises. I don't go to the gym anymore nor even do the easy in-house exercises. I don't write anything that's not work related.

I do not envy the super rich and celebrities. Just the few meeting requests, emails, chats, texts and phone calls I get by being a little visible in my tiny segment of data analysis space is almost running me crazy. Even though I try not to offend people or come off as arrogant, I still had to slowly retreat from many requests. Some are from friends wanting to catch up. Some are from people wanting to give us business but love meetings. And a few from people I respect highly and who helped me in my starting days with referrals and mentor-like advice; I came to a juncture where I had to decide to not have to disappoint them by staying a bit far from them.

Why this particular new year resolution?

What I want in life are very simple and I have always known them since I was 13. Writing is one and being rich is not. Sadly, most of all I do these days makes it look the other way round. And with having a proper (no longer solo) business, I don't see myself getting away from the pursuit of money. So at the minimum, I should write more. Creative writing. Even if it means adding more to my burdens.

When I was much younger, filled with too many African Writer Series novels and Shakespeare and Jane Austen and Charles Dickens and Louisa May Alcott and Jonathan Swift and Mark Twain and Plato's Dialogues, I thought I would have written a novel like Things Fall Apart before I reach age 30. A novel like the Great Expectations by Charles Dickens. One that shows future generations what life was like in our generation. A book a girl in year 2120 would read and understand the Nigeria of today.

The pattern my daily schedules are taking is not one that gives me any room to achieve that goal. So I am attempting to take back some control and keep the aspect of me that has been existing before adulthood (with its responsibilities).

Microsoft has included some new formulas in Microsoft Excel that I call "magic formulas".

They are:

UNIQUE
FILTER
RANDARRAY
SEQUENCE
SORT
SORTBY

In today's post I am going to introduce you to SORT.

The name already says it all. And some people might wonder: why would anyone want to use a formula to sort?

Remember the first post in this series, where I talked about UNIQUE.

Wouldn't it be nice to have the output of the UNIQUE list of products sorted alphabetically? You can download the practice along file at https://urbizedge.blob.core.windows.net/urbizedge/SORT-%20new%20formula.xlsx

Meet SORT
Just wrapping SORT around the UNIQUE formula, you get a sorted unique list of products.

Cool, right?

The magic doesn't end there. SORT allows you to specify column to sort by, the sort direction and whether row-wise or column-wise.

Below I used SORT on the entire sales report table.

You can watch a short video demonstration of it: https://youtu.be/P8vJe9iW9VU

Microsoft has included some new formulas in Microsoft Excel that I call "magic formulas".

They are:

UNIQUE
FILTER
RANDARRAY
SEQUENCE
SORT
SORTBY

In today's post I am going to introduce you to UNIQUE. It can be the end of your troubles with PivotTable for use with growing/changing dataset. And it can be the butter to your SUMIFS, COUNTIFS and many other high-level report creation formulas.

Scenario

Imagine you need to create a live report of sales from raw data that keep getting updated (or is growing). Download the practice along file at https://urbizedge.blob.core.windows.net/urbizedge/UNIQUE%20-%20new%20formula.xlsx

You could use PivotTable to create this:

The sad part is that whenever new data is added, you will have to manually include the new data range in the PivotTable or do manual refresh if you had used "Format as Table" on your raw data before Pivot Table creation.

Alternatively, you could have used Remove Duplicates, SUMIF and COUNTIF. Again, upon new product addition, you'll have to redo the Remove Duplicates.

Meet UNIQUE

UNIQUE is the solution you want.

It does remove duplicate but in a dynamic way. If you don't want to select unused cells in order to capture new entries, then just apply "Format as Table" on your data. But for this demonstration, I am going to show without formatting as table while in the video I demonstrate both situations.

Simply typing UNIQUE(range_of_cells) creates the remove duplicate equivalent of the product field, giving me the unique list of products sold.

Now I can merge it with other regular formulas like SUMIF and COUNTIF to get the sales per product and count of transactions.

In the criteria, I select the entire output of the UNIQUE formula. This automatically changes it to first_cell_address_# (e.g. K2#). That # is a new feature to let you know that it's working on an array output.

I do likewise for SUMIF to get the sum of quantity and sales amount per product.

You can watch a short video demonstration of this: https://youtu.be/QLgwXVkALFg

In April 2020 I will, alongside 13 other global experts, be presenting at the Olympia, London for the first Global Excel Summit.

I will be presenting on how to earn high consulting income in low income countries. An interesting topic. I figured since many people will be presenting on technical sides I should pick a topic I am uniquely experienced in.

And since the start of my Excel consulting journey in 2012, I have seen a lot and skillfully moved from getting peanuts for my expertise to charging as high as N357,000 for a 1h45mins talk. And close to a million Naira for a one day teaching job. Though now, there are lots of overhead, a few full-time staff and some expensive solutions we are developing that gulp all the income but without properly positioning myself to earn high consulting income, we would never have moved to having full-time staff nor developing mass market solutions.

Truthfully, it is not all my consulting engagements that pay well. The figures I quoted above are the outliers (rare exceptions). We still have some clients who do pay not much but we have figured out a way to get more than the industry average, what the typical consultant in our field in a country like Nigeria gets. We have figured out an excellent way to turn our consulting service to a high margin good volume product and attract the right type of clients in a way that we can get above market rates from them.

I would be sharing all those strategies during the presentation. I will also mention how I get, with no marketing work on my side, $55/hr at my free time gigs.

To register, visit https://globalexcelsummit.com/ and you can use my discount coupon: Michael-10%

Ever thought of creating an interactive form in Microsoft Excel? With clickable radio buttons, dropdowns to select from a predefined set of options, an image file picker to attach/import a passport photo, a calendar that pops up to let the user pick a date and many other useful features. Today is your lucky day.

You can watch the video tutorial here: https://www.youtube.com/watch?v=SRUiFpbtBgQ

All these amazing tools are housed in the Developer menu. So the first task you have to do is enable/activate the Developer menu. Go to File, Options, Customize Ribbon and tick the checkbox beside Developer.

In the Developer menu, you will see the controls that make interactive forms possible -- from radio buttons to checkbox to calendar to picture frame and many more.

Watch the video to see me do a comprehensive live demo. And I included a bonus on how to make what the user type in one part of the document appear in other parts of the document. This is a neat trick for creating legal or contractual documents. You ask for the other party's name and that name appears in all the necessary parts of the documents. And once you change the name in the first part, it changes in all the other parts.

You can watch the video tutorial here: https://www.youtube.com/watch?v=SRUiFpbtBgQ

Originally, I shared this on a Telegram Finance and Investment group (which you can join via https://t.me/joinchat/Gfyf4VFBAOHYM3kdc98QHA

I have been watching RealVision.com series on the global economy and gleaned the following insights:

The global economy is slowing down. All the numbers are indicating that Germany is nearing a recession, US manufacturing is down. Europe, generally, is having a very tough time due to many localised issues across the countries there and it is generally weighing down the European economy. Global banks are shrinking their activities to avoid 2007/2008 styled contagion of financial distress.

China is slowing down at a rate more than what the government officially publishes. For the last one year, the govt controlled financial sector has been pumping cheap loan out to create artificial economic wealth — keep comatose companies alive, keep companies expanding, fund large scale infrastructure and mute the effect of lower consumer activities.

US Dollar is considered as overpriced (too strong). The negative and zero bond yields in Japan and Europe has driven a lot of demand for US financial market assets. The emerging and frontier economies have felt it the most, seeing their currency depreciate significantly in the last 2-4 years. There is a lot of push to weaken the US dollar — from the White House as that will make their trade war with China (and other countries) more effective as per their desired economic effect, for the Fed as that could provide the next economic boost, and for investors as it means general asset price/value increase.

Gold is considered as being underpriced due to monetary policies that are aimed at reflating the financial markets whenever there's some downturn. Also, the strong US dollar is muting the price level.

Bitcoin is considered as very underpiced. Bitcoin has a market cap of $149B. If it were a private company listed on the stock exchange or a startup, it would have valuations in very many multiples of that. It's userbase is well beyond than of many companies with larger market cap, it's adoption rate is very high and it is powered by technology+infrastructure that runs into billions of USD.

Another angle of view of some of the portfolio managers is that Bitcoin is not correlated with the broader investment market and provides a better alternative to holding physical gold as against gold futures that many still see as tied to the financial markets systemic risks.

One important consensus is that Bitcoin is gaining more ground in the mainstream investment funds world. More institutional investors and family offices are holding positions in it. And this would only continue to accelerate.

There is a new global drive towards another version of QE that is built on Modern Monetary Theory (MMT). So the concept is to remove the classical limitation to printing more money and expansionary spending by government because of inflation worry. The theory states that inflation is not directly/naively linked to printing more money as usually said. And the last 2 decades is a solid proof. The theory says as long as it is not done past the level where the economy reaches full employment and GPD potential, the printed money would only unlock greater factor utilization and economic growth.

So now central banks will start printing money more than ever before. Christine Lagarde is regarded as a monetary policy evangelist, so ECB will do even more than would have under Draghi. Japan is tilting towards deflation and they are now going back into aggressive QE. China is also being propped with articifical money which they are now doing more of as they try to devalue the yuan to mute the effects of US tarrifs and weaker exports.

The worries are that there is no proven method to ensuring the money printed primarily impacts the real economy and not just inflate financial assets prices. One thing is sure though, there will be more financial asset price propping. Even if/when a recession hits globally, these artificial supply of money will not let prices be as depressed as they ordinarily would have been nor stay depressed for as long as they historically would have (pre- QE)

Practical takeaways I am considering are:

1. Do more Bitcoin holding. If US dollar goes up, my Naira value of Bitcoin goes up. And if US dollar is devalued, higher chance of seeing it reflect in my Bitcoin value than in holding plain US dollar.

2. Hold and add to my Gold positions. Strong fundamentals and narratives for good upside.

3. Stay clear of Oil. Weakening global economy (especially the manufacturing, logistics and fossil fuel based sectors) limit the upside to Oil. Even despite the Saudi Aramco facility bombing, Iran tanker hit and other issues that typically create upside speculation, Oil price has gone down to below the Saudi Aramco cut price surge even though there are narratives that there is more supply from reserves than is claimed by Saudi in supply/production recovery.

4. Stay out of Nigerian stocks until price/volume action indicates a strong price recovery. It is not just enough to spot high dividend yields, it's the future guidance (future dividend yield) that matters and when compared with capital loss vs capital preservation and sure Fixed Income yield vs unsure future Dividend Yield; I will want to be less optimistic about stocks till I see improved economic fundamentals/policies and return of investment capital to equities.

5. Do more of money market. I don't see yields going down as we will have to struggle to keep inflation from not rising while trying to attract FPI's with good real rate of returns.

6. Keep my expenses as low as possible and in Naira. PPP vs nominal exchange rate favours spending in Naira.

7. Derivatives on NSE. Watch out for the derivatives NSE is saying they are launching soon (they still said it during last month's conference type event).

8. Add to my US treasury bond holding. There is a general consensus that the developed world is heading to zero percent interest rate in the short to medium term. So far US is the only one not yet there. And as the rate differential is being arbitraged away, the price of its bonds will go up.

Last year's was amazing. And we are planning towards having a more amazing one this year.

You can register to attend via https://meetup.com/Nigerian-Excel-Users-Meetup/events/265613996/

If you are interested in giving a presentation, then kindly fill the presenter's form via https://forms.gle/g2A2faEfM5BA2k8N7

The full details are (till we get the verified presentation topics and presenters):

Nigerian Excel Users 2019 Meetup

The Zone Tech Park, Plot 9 Gbagada Industrial Scheme, UPS b/stop, Gbagada Expressway, Lagos.
23rd November, 2019 (09:00am to 3:30pm)

Start Time: 09:00am

1) Introduction and networking (25 mins)

2) General Discussions pt 1(Your Excel Journey, career advantage, Excel pet peeves, ideas, the esoteric, 30 mins)

3) Presentation 1 (25 mins) + Q & A (10 mins)

4) Presentation 2 (25 mins) + Q & A (10 mins)

5) Presentation 3 (25 mins) + Q & A (10 mins)

6) Break (10 mins)

7) Presentation 4 (25 mins) + Q & A (10 mins)

8) Presentation 5 (25 mins) Q & A (10 mins)

9) Presentation 6 (25 mins) + Q & A (10 mins)

10) Break (10 mins)

11) Presentation 7 (25 mins) Q & A (10 mins)

12) Presentation 8 (25 mins) Q & A (10 mins)

13) General Discussions pt 2 (Your Excel Journey, career advantage, Excel pet peeves, ideas, the esoteric, 30 mins)

15) Close (5 mins)

End Time: 3:30pm (Networking continues)

There will be drinks and snacks.

Don't forget to register to attend via https://meetup.com/Nigerian-Excel-Users-Meetup/events/265613996/

A big thanks to all who were able to make the live broadcast. Especially, for bearing with the initial broadcast hiccups. You can watch the recorded session at: https://www.youtube.com/watch?v=CEAA7ar6xfM

Below are the slides and resources from the broadcast session. The next one would be on Equities Investing.