Limitation of MATCH function. Let’s substitute Python for Excel!



STUDY AT HOME  ~ investment analysis & its procedure as tech hobby ~ Part 2

In order to get how long S&P500 has spent on recovering particular level of stock price, I tried to make use of MATCH and VLOOKUP functions of Excel. MATCH is famous to search value matches (or most matches) the given value across rows in column. VLOOKUP is also well known as bringing value in other columns at the same row as a given value.

For instance, I was planning to identify the first date when the price of a given date exceeded itself by the two functions. MATCH had been used to look for the first exceeded price to a given price in the later dates. Then VLOOKUP had been functioned to retrieve its date.

However, I realized a fetal restriction of MATCH. Ye gods! The order of given column applied by MATCH must be ascend or descend. Stock price is a beast. It is going up and down day by day. It is never monotonously changing. In other words, MATCH function is not applicable to my idea.

Even a tech giant, Microsoft, is not perfect. I was upset but I thought “isn’t it a good opportunity to challenge programming?”. I have been interested in that for a long time. Although I studied to code Python as hobby, the most difficult thing was to find the idea to make use of program. I made a decision to try to resolve the problem by Python.

Next article – Google Colab makes us free for environment setup >>