A super beginner who does not know the basics of Python tried to graph the realized profit and loss data of Rakuten Securities in Python
I tried to graph the realized profit and loss data of Rakuten Securities.
Realized gains/losses are gains/losses that are fixed by selling or settling the shares you hold . Until then, it is called unrealized profit/loss , and the profit/loss will be fixed by cashing.
In other words, by checking the realized profit and loss, you can see whether you have gained or lost. Let's look back on past results and make use of them in future investments so as not to make the same mistakes.
- Original article → Graphing realized profit and loss data of Rakuten Securities in Python [learning memo]
Get csv file
First, get the realized profit / loss data (csv file) from Rakuten Securities. Log in to Rakuten Securities and open Account Management → P & L / Tax History → Realized P & L.
When the following screen is displayed, click "Save in CSV format" at the bottom right of the screen to save.
As shown below, I was able to obtain a csv file of realized profit and loss from Rakuten Securities. (It's hard to see, but it's 99% lost.)
* Notes on csv files
Although it is a csv file brought from Rakuten Securities, in fact, all data types other than "brand code" are "object type". (Check the data type with dtypes)
For data types, see the following articles.
Since the calculation cannot be performed as it is, it is necessary to convert "object type" to "int type". Let's convert the type using Python.
I wanted to say, but I couldn't do it. So, this time, I took the painstaking measure of converting directly to "int type" in the spreadsheet.
Open the file in a spreadsheet and set the display format to "Automatic".
You have now converted the "object type" to the "int type".
This is a future issue.
If there is a kind person who says "I can do it this way!", I would appreciate it if you could let me know.
Creation procedure
Now let's read the csv file and graph the realized profit and loss.
Click here for the completed code.
from google.colab import drive
drive.mount('/content/drive')
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_csv('drive/My Drive/csv/rakuten-int.csv', encoding='utf-8')
df.head()
df.dtypes
df['Realized profit / loss[Circle]'].cumsum().plot(figsize=(8,6),fontsize=18)
plt.xlabel("Number of transactions",size="12")
plt.ylabel("Realized profit/loss",size="12")
plt.grid(True)
plt.show()
First of all, let Google Colab read the csv file.
However, since Google Colab runs on the Internet, it is necessary to devise to read the csv file.
Execute the following code
from google.colab import drive
drive.mount('/content/drive')
It means to mount Google Drive in a folder called "drive" under the "count" directory. Google Drive files are stored in the "drive/My Drive" folder under the folder "drive" specified here. When you execute this code, the authentication URL will appear, so please proceed to the update as described in the following article.
[Google Colab] How to read a file [Can be done in 3 minutes]
After the update, import pandas and matplotlib to load the csv file.
Import pandas and matplotlib
import pandas as pd
import matplotlib.pyplot as plt
Substitute csv file for df
df = pd.read_csv('drive/My Drive/csv/rakuten-int.csv', encoding='utf-8
')
read_csv : Used when reading csv files 'drive/My Drive/csv/rakuten-int.csv' : Relative path to csv file encoding = ’utf-8’ : Character code
Is there a problem with the character code of the csv file brought from Rakuten Securities? Because there is, an error occurs. Be sure to enter "encoding ='utf-8'" after the relative path. Please check the following article for details.
Active engineers explain how to avoid unicode decode error in Python [for beginners]
Check if the data was acquired
df.head()
I was able to get the data.
Graphing realized profit and loss data
df['Realized profit / loss[Circle]'].cumsum().plot(figsize=(8,6),fontsize=18)
plt.xlabel("Number of transactions",size="12")
plt.ylabel("Realized profit/loss",size="12")
plt.grid(True)
plt.show()
df ['Realized P/L [Yen]'] : ['Realized P/L [Yen]'] in df cumsum () : Cumulative sum (the sum of the sequence from the first term to the nth term) For example, it is a calculation that outputs the cumulative sales data up to that day from the daily sales data.
Plot x-axis labels
plt.xlabel("Number of transactions",size="12")
Number of transactons : x-axis name (“number of transactions” in Japanese) size : Font size
Plot the y-axis label
plt.ylabel("Realized profit/loss",size="12")
Realized profit/loss : y-axis name (“realized profit/loss” in Japanese)
The label of the axis is written in English, but if it is written in Japanese, the characters will be garbled. I really wanted to write it in Japanese, but it's going to be long, so I'll take another opportunity.
plt.grid (True) : Display the grid on the graph plt.show () : Display the created graph on the screen
Here is the execution result so far.
I was able to graph the realized profit and loss data.
It is poor from the stunning W bottom. It is very easy to understand if you visualize it with a graph.
At one point, it went to nearly minus 200,000 yen, but it recovered sharply and recovered to nearly plus 150,000 yen, and ended up with a minus.
By the way, the final realized profit/loss is -5,091 yen. (Ended on April 30, 2020)
I lost about 5,000 yen over a year. It's cheap if you think of it as a study fee.
Future tasks
- Convert "object type" to "int type" using Python
- Make the axis label in Japanese
- Set realized profit / loss to ± 0 or more
I would like to acquire more Python skills so that I can perform detailed analysis. And we will continue to do our best in asset management so that we can show you transaction data that is not embarrassing.
Recommended Posts