Disclaimer: the discussion in this post is for personal sharing and informational purposes only and should not be relied upon or thought of as investment advice. If you need help on your investments, please work with a professional advisor.
About three weeks’ back, I wrote a lamentation — Nursing My Portfolio Back to Health — weeping over the sudden deterioration of the health of my investment portfolio, which was (and still is) holding the majority of our liquid assets.
Since then, governments around the world have brought out massive fiscal Yamato cannons to resuscitate the economy. (Sorry for the Starcraft reference, I just couldn’t help it.) This effectively placed an inviolable put on global stock prices, flushing out the bears in the process — many of whom burnt by the unforgiving surge driven by short covering.
For perennial bulls like me, the latest rescue was like a breath of much-needed oxygen in dire times. While the paper losses are still significant — still exceeding a few months’ labour — we were able to bring down the average purchase price of key counters dramatically on the way down. I know that catching a falling knife is not the most pleasant of experiences, but we wanted to ensure that we were able to capture some opportunity, even if it means temporary pain at the initial stage.
Also, the vindication that came in the intervention phase was definitely well worth it.
If you are a DIY investor and would like to keep a closer tab on your portfolio without too much manual, repetitive work, Python does have a few nifty libraries that could help connect to, say, MS Excel, to analyse your portfolio.
To begin, we import pandas to help us manage spreadsheet-like dataframes, as well as pandas_datareader to provide us the means to connect to the Yahoo! Finance server API.
To ensure that all our counters are properly labelled, line 4 above defines a dictionary counters to list down key-value pairs of each counter and the corresponding financial data, by way of dictionary comprehension.
For this illustration, I use the list of FAANG stocks — Facebook, Amazon, Apple, Netflix and Google — as examples for this demonstration, but you could have your actual list of stocks that you own or are monitoring instead. If you are unsure of the ticker name of the stock you are looking for, you could use the search bar in Yahoo! Finance quite quickly to find out.
For instance, by typing in ‘Google’ you should be able to find the following information:
The following three lines involves extracting the latest closing prices, either the last known price — usually with a circa 15-minute lag if the market is open — or last closing price otherwise. Following extraction, we convert the data structure from a dictionary to a small dataframe, subsequently renaming the column for good measure.
The final step would be to export the data into a format readable by MS Excel, in xlsx.
Pandas has methods ExcelWriter and to_excel that allow us to save an Excel file by passing a string as the file name to the former — by default saving to the base folder of the interpreter’s default environment unless defined within the string. The engine used in this example is ‘xlsxwriter’. In the latter method, we could provide the sheet name by defining in the namesake argument. We make sure we save the file.
If executed correctly, you would see an Excel file created and saved. When opened, the following should appear:
There you have it. Following this step, the rest of the analysis could be Excel-based. Do try it out to see if it works. While I can’t be responsible for any mishaps that might happen — e.g. I can’t guarantee that the API link would always be accurate and running, especially since it’s free — I find it to be a helpful resource in automating my portfolio analytics in a meaningful way.