Portfolio performance: some issues and questions about lares

[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.