Portfolio performance: some issues and questions

[ElKron]

Hi Bernardo,

I tried out your portfolio performance functions. The graphs are very useful in conjunction with the automatic data scraping and processing. I have some questions and uncovered some issues you could have a look at if you have the time. I also have some suggestions for functionality but will put that in a separate issue,

Issues:

• Christmas bug: sometimes in the period around Christmas some quotes disappear which has the effect that the daily performance (ROI) can be -100% or +1100% for some of these days. Below is an example from my experience.
• 3 tickers that I used to invest in, were not found: PHAG.AS, PHPD.AS, ABLX (or ABLX.BR). The first two should be available since they are still traded on the Amsterdam stock exchange. See here and here as reference. I didn't find them myself on Yahoo Finance, so I don't know if it can be fixed easily. The last one is on Yahoo Finance but the company was taken over and removed from the stock exchange so I am not sure if you could still incorporate this data. -> Since I closed these positions, I could add the transactions manually to the deposit tab (fondos) but this would confound the ROI results. Do you have suggestions?
• Foreign stocks: at the moment, I only invest in stocks in euros. In the past, I have invested in stocks in dollar and swiss francs also. As far as I can tell, the package does not take into account the exchange rates which change the values of the stocks in euros. Is there an easy way to take this into account or use this consistently in the package?
• Dividends: dividends are recognized in the scraped historical info but get lost when merging with the transaction data (_dailystocks function) so they are not taken into account.

Questions:

• How should I treat closed positions? Do I put these tickers in the Portfolio tab of the Excel or only in the transaction history?
• Is there an easy way to obtain the total value of your investments + leftover cash from deposits per day? It would be nice to have internal rate of return calculations on these values (see upcoming suggestion).
• Is there a way to add costs not related to transactions outside of the deposit tab? E.g. broker costs for using certain services. For me, they are part of the ROI with respect to my deposits so I would prefer to not put them there.

Sorry for the long post. Thank you for all the help!

ElKron

[laresbernardo]

Hi @ElKron Thanks for your feedback and comments. I'll start commenting below:

• All quotes are the result of querying Yahoo's free API, using quantmod's src = 'yahoo'. Google Finance stopped providing data in March, 2018 so that wouldn't be an option. And the other ones available are paid or need further settings, thus I'd recommend using quantmod library instead. So, the quote values are never changed inside the library. I'll try to check those outlier picks and see a logical way to skip them.
• ROI: I have a similar situation. In my current role, my company gives us actions every quarter (which I don't pay). So, I add them in the Excel file as if I'd deposited and bought the actions for the same price, the same date. You could try a similar trick for those actions you sold! (I'm supposing "closed" is the same as "sold" and you received the money back into your account as cash).
• Regarding the currencies, I think it is a nice to have but will definetly take a lot of time thinking it through in a way that works for everyone. As I haven't needed it, I haven't developed or coded anything here. Feel free to clone the project and request a pull if you'd like to collaborate.
• I did not understand the dividends question. I think they are not lost anywhere. Do you have a reproducible example?
• Yes: note that when you run the stocks_obj() function, you'll get a list object with 3 dataframes and a list with all plots. In the portfolio dataframe, you'll have your daily ROI, Portfolio overall balance with and without Cash, etc.

  
  > lapply(dft, names)
  $quotes
  [1] "Date"    "Symbol"  "Value"   "Div"     "DivReal"

$stocks [1] "Date" "Symbol" "Value" "Quant" "Each" "Invested" "Cost"
[8] "Dividend" "CumValue" "CumInvested" "CumROI" "CumQuant" "DifUSD" "CumDividend" [15] "CumCost" "Weight" "Type"

$portfolio [1] "Date" "CumInvested" "CumValue" "CumCost" "CumDividend" "ROI" "Invested"
[8] "Dividend" "Cash" "DifUSD" "CumCash" "Portfolio"

$plots [1] "roi1" "roi2" "growth" "summary" "change1" "change2" "types"

- And for your last question, I'd simply add those up in the transactions tab with a minus. So instead of deposits, those costs would be negative deposits.

[laresbernardo]

So, my homework here would be:

• Try to get a way around those outlier picks so the data returned by Yahoo's API does not get modified in anyway but the plots make sense, especially daily ROI.

Good-to-haves that I will NOT invest my time in right now:

• Logic and development around different currencies in a portfolio (may use lares::get_currency() when developing)

[ElKron]

Thank you for all the help!

I have 2 follow-up concerns:

PortfolioDummy.xlsx

• For the dividend issue (dividends not present in the data or portfolio value), I added an example portfolio. BPOST.BR is a dividend-paying stock which is registered when downloading the ticker data from Yahoo. However, subsequently it is lost in the mutate calculations.

• When ticker data is not available/complete on a date of transaction, that transaction is not registered in the tool. As an example, see the tracker in the same dummy portfolio. Yahoo does not give data on the latest transaction date causing it to not to be registered in the tool even when later data is available. The stocks dataframe indicates that 500 shares of the tracker are the latest quantity instead of the supposed 1500 including the latest transaction. I understand that portfolio/stock value is not available or incomplete for dates where Yahoo is incomplete. But is it possible to make the cumulative amount of shares still consistent over later dates?

Code you can use to check the above:

library(lares)
df <- stocks_file(filename = "PortfolioDummy.xlsx")
df$transactions$Date <- as.Date(df$transactions$Date)
str(df$transactions)
dfp <- stocks_obj(df)
dfp$stocks

In any case, thank you for your time and help!

[laresbernardo]

• After debugging for a while, I think I've found the issue: actually was a consequence of the previous fix... now it should work!
• Actually that's how it was intended.. having repeated rows per day if nothing has changed doesn't add much value to the data. Now, the issue seems to be in the cumulative values, it sure is a bug.. give me some time and I'll fix that

[laresbernardo]

@ElKron done! With the latest fix, you should have the correct cumulative value even though for some days you have no quotes over some of the stocks. Cheers and thanks for reporting this case!

[ElKron]

@laresbernardo ,

The dividends work like a charm indeed! Many thanks!

The cumulative stock quantity however is not yet fully fixed (see previous example Excel). The issue lies in the following line daily <- hist %>% left_join(trans, by = c("Date", "Symbol")) If the history does not contain the same date for a specific symbol as is present in the transaction data, the quantity sold or bought will not show up in the daily dataframe. Maybe another join function (full_join?) might help, but then the follow-up data wrangling code needs adjustments. I do understand that you don't want repeated rows though.

Sorry to bother you with these tedious bugs and thanks for all the help...

[laresbernardo]

Ok, please do check again. Should be fixed by now to work with your portfolio @ElKron A full_join wouldn't work. I did an expand.grid over all dates and existing Symbols so every day has all possible tickers, and tidyr::filled up the values.