UNION is an interesting DAX formula. Simple yet powerful in what it makes possible for you to achieve. 

I will be demonstrating with a simplified version of one of my real world use cases for UNION.

Say you have created a Waterfall chart showing cashflow (or inventory quantity or staff addition/reduction, sales quantity) movement month on month through out the year and with the final position as at year end.

The (simplified) source table looks like this

But you want to see the opening cash (or opening stock or staff count at start of the year etc).

There are many ways to get it in if you are given that data but the way we will explore today is using UNION formula.

UNION is a DAX formula that allows you to append/join two or more tables. And in this demonstration I will show you a cool way to create a literal table in Power BI DAX (a table with rows of data that you type in yourself in the DAX).

Say you were given the opening cash position as N2m and you just want to plug that in and move on with your analysis and chart. If it were Microsoft Excel, that will be as simple as adding a new row in your table. How do we achieve the closest to that in Power BI?

Union Table = UNION('Cashflow Table',{(2019,"Opening Cash",2000000,0)})

{(2019,"Opening Cash",2000000,0)}

is the literal table part. And if I wanted multirow table it's just to put each row in the () separated by comma. For example:

{(2018,"Closing Cash",2000000,0),(2019,"Opening Cash",2000000,0)}

With this new table I can create the type of waterfall chart desired. Note though that if you have a very large table, you might want to create a UNION with a SUMMARIZE 'd copy of the table rather than duplicating the entire table.

You can watch the much more engaging video demonstration of this: https://youtu.be/zNCYmSLM2cg 

I have been switching between DSTV Now, Showmax and Netflix these last 6 months. And in today's post I'll give you my own review of the different offerings.

DSTV Now
If you already have DSTV then you should try the DSTV Now. It allows you to access your DSTV subscription on your computer, tablets and phone. And if you are part of a family with an active high cost DSTV bouquet, then this is a good choice. 

Mutltichoice included a value add. You can watch on demand movies in addition to the live TV. I loved this extra. And they put very good movies on a weekly basis that you can browse through and watch the ones you like. 

Just know that you'll have to link the DSTV Now to your smartcard and DSTV subscription.

Showmax
Showmax is Multichoice competition to Netflix. For less than what Netflix charges you'll get access to lots of movies (maybe thousands). 

They've got a large collection of high quality TV series. Lots of HBO series. Including Game of Thrones and popular (newer) HBO series. That's one thing they have that Netflix doesn't have. Another plus over Netflix is that all their movie collections are available for Nigeria. Unlike Netflix where many of the good and new movies are not available for Nigeria (but available for US) even though it's same monthly charge. A third plus for Showmax is the much richer collection of African movies and series. Even the Big Brother Show was available on Showmax.

One negative. though slight, is that not all the movies on Showmax have subtitles. I love subtitles. It's part of the reason I stopped going to the cinemas. Another issue to grapple with is that locating the good movies can be a lot of digging on Showmax. What I do is I search using the name of popular actors/actresses. With that I was able to locate very excellent movies I never got to see by just scrolling or browsing the genres.

Netflix
Netflix has the largest collection of movies compared to the other two.

They've got excellent series. But not always true for the movies for those of us in Nigeria. For every one very good movie you find there are 3 you won't find but someone else in US will find on Netflix. So I have just resolved to running a concurrent ExpressVPN subscription so I can always change my location to US or Canada and be able to see more great (and new) movies.

Another negative I have with Netflix is that their self-produced movies (not series) are crappy. I have tried many times to locate an exception and I failed each time. They are great at intriguing movie themes and even story line, but still the entire outcome is poor. I just avoid them even when there is an A-list actor in it. 

Netflix is more expensive than Showmax. But compared to DSTV Now, it will depend on the DSTV bouquet you are on, though I can say at a value for money parity, Netflix is cheaper.

image: dataquest.io

Nowadays, I see bankers, ICT engineers, project managers, sales managers, finance managers, HR managers, lawyers, accountants and many people already deep into a different career path all wanting to switch completely to data science.

I don't know where the pressure is coming from but some of them don't know that doing that switch can be retrogressive -- taking a lower grade/paying job and limiting your career growth. 

In the corporate world, a data scientist is HR managed as a data analyst using new set of tools. To a large extent, the same career paths await both. As a data scientist you will always work under a manager and it could the someone who is a project manager like you were before you made the switch. So that's the lower grade possibility I mentioned.

Also, your hiring is focused more on you doing a technical task more than on you doing a manager-level task, and it can be a very career limiting positioning. You are seen as data analyst with more powerful tools and new age skills. You will almost always have to answer more to other people and have (usually) no one to answer to you. You work back-end, the IT type of back office role.

If you do this (actual data science work) for too long (say 5 years), then there is a problem. Stagnancy. And that's because any significant career progression in a non-consulting company would mean you doing less of the actual data science work but managing other people to do them.

Let's call all what I have said - side 1 (of my opinion coin). I said it with the assumption that you do indeed learn at this your old age and limited free time to become a data scientist.

Side 2 is the expectation of how long short it would take them to be a proficient data analyst.

Taking a couple of courses will definitely help you stand out better than another candidate with same situation and ambition. But if I am looking to hire a data scientist, the skills I would be looking for is way beyond courses and training you have attended. I would be looking for real world project portfolios. I would be looking for your knowledge contribution in that space (deployed projects online, tutorials you post online, communities you are part of and active problem solving skills that demonstrate your mastery of the methods + tools). But then again, most companies in Nigeria are not really seeking Data Scientists. They are just looking for regular data analysts that can use more than Excel. Maybe Power BI, SQL and familiar with Python or R. And on the job you'll just end up using Excel and a BI tool. But as usual, they'll rather exaggerate the competence requirement. 

I see many people get that type of jobs and I see many of the bankers, ICT engineers, project managers, sales managers, finance managers, HR managers, lawyers and accountants actually think that is Data Science. 

Data Science. A Data Scientist is someone who would use more of python and R, build predictive models and deploy as products or integrated tools powering major processes within the company. Not someone doing daily and weekly reports with presentation and BI for management. 

In an insurance company, a data scientist would be building programmatic models that analyse risk and create on the fly customized insurance product for the insurance company's potential clients. So John can be on the insurance company's website and fill a well tuned form, and instead of being presented with static buckets of insurance covers he gets a cover fine-tuned for him with a premium cost that is unique to him (computed by a data powered dynamic algorithm). While the folks tasked with creating the day-to-day operations and finance report - regardless of the fancy tool they now use - are just data analysts.

In a lending or financial service company, a data scientist builds the model that predict default risk, do customer segmentation and do product offering simulation to determine perfect pricing on an ongoing basis.

In a telecoms company, a data scientist would model product offerings, do customer segmentation, do churn prediction model, build algorithms that power marketing activities and sentiment analysis.

The type of education and preparation to become a data scientist is nothing less than 9 months full-time (not spare-time) learning, online project contributions and real world projects building. And with a good (diploma level) grasp of statistics, algorithms, data structure and predictive modeling (machine learning). Anything less and you are not a data scientist.

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