CHAPTER 3: DATA ANALYSIS WITH EXCEL
|
|
To build a backtesting spreadsheet, you will need
to import historical data into Excel. In this chapter, we will cover the
process of importing data from external sources and how to clean and
transform the data for analysis.
There are several sources where you
can download stock historical data. Here are a few popular options:
1, Yahoo Finance: Yahoo Finance provides free historical price data for most
stocks, which can be downloaded in a CSV format. Simply go to the Yahoo
Finance website, enter the ticker symbol of the stock you are interested in,
and select the "Historical Data" tab. From there, you can download the data
for a specified time period.
Open the .csv file, delete the “Adj.
Close” column. “Adjusted close” is the closing price after adjustments for
all applicable splits and dividend distributions. We don’t need it in our
method.
Click any cell in the “Date” Column. Sort the "Date" as Newest to
Oldest.
Select and Copy 6 columns including Date, Open, High, Low, Close and
Volume historical data.
Open the Demo Excel file that contains the VBA Macro for backtesting,
click the "Pick" worksheet tab, select columns H to M that correspond to the
6 columns storing the Date, Open, High, Low, Close, and Volume historical
data. Then, paste the “Value” only (don’t bring the .csv files’ format into
the Demo Excel file) into these selected columns.
Save the Demo Excel file. We don’t need the Yahoo .csv file anymore, so
close it. Now the targeted stock’s historical data has been filled in the
Demo Excel file, and it is ready to run the VBA Macro program for
backtesting.
2, MSN.com: You can download historical stock prices
from MSN Money by following these steps:
Go to the MSN Money website
(https://www.msn.com/money).
Enter the ticker symbol of the stock you
want to download data for in the search bar and click "Search".
On
the stock's page, click on the "History" button, sometimes it is hidden
inside the three dots button in the top right corner.
In the
"History" section, select the time range you want to download data for.
The historical data will be displayed on the page. Select and copy the
data, paste into Excel sheets.
Note that not all stocks may be
available on MSN Money, and the historical data may be subject to delays or
inaccuracies. It's always a good idea to double-check the data before using
it in your analysis or trading strategy.
3, Google Finance: Google
Finance also provides free historical price data for most stocks. Simply go
to the Google Finance website, enter the ticker symbol of the stock you are
interested in, and select the "Historical Prices" option. You can then
download the data for a specified time period.
You can download
historical stock prices from Google using Google Sheets by following these
steps:
Open a new or existing Google Sheet.
In a cell, enter
the following formula:
=GOOGLEFINANCE("ticker", "attribute",
"start_date", "end_date", "interval")
where "ticker" is the stock
symbol you want to download data for, "attribute" is the price or volume
attribute you want to download (such as "open", "high", "low", "close",
"volume", or “all”), "start_date" is the date you want to start downloading
data from (in the format "YYYY-MM-DD"), "end_date" is the date you want to
stop downloading data at (in the format "YYYY-MM-DD"), and "interval" is the
frequency of the data you want to download (such as "DAILY" for daily data,
"WEEKLY" for weekly data, or "MONTHLY" for monthly data).
For
example, if you want to download the daily closing price data for Apple
stock (symbol "AAPL") from January 1, 2012, to December 31, 2022, you would
enter the following formula in a cell:
=GOOGLEFINANCE("AAPL", "all",
"2012-01-01", "2022-12-31", "daily")
Press Enter. The formula will
automatically download the historical data for the stock and display it in
the cell.
If you want to download data for multiple stocks or
attributes, you can copy the formula to other cells and edit the parameters
as needed.
Note that the GOOGLEFINANCE function may not be available
in all regions or may be subject to certain limitations. Additionally, the
historical data downloaded using this method may be subject to delays or
inaccuracies, so it's always a good idea to double-check the data before
using it in your analysis or trading strategy.
These are other
options for downloading stock data, but need to pay some fees. It's
important to note that the availability and quality of data may vary
depending on the source, so it's always a good idea to do your research and
make sure the data is suitable for your needs before using it.
After
importing all your historical stock data into Excel, you may start to feel
like a data analysis wizard. But be careful, with great power comes great
responsibility... to not accidentally delete all your hard work with one
misplaced click of the mouse! So, remember to save your Excel file often and
always have a backup, because you never know when Excel will decide to crash
on you. And if all else fails, just blame it on the stock market. It's
always the stock market's fault.
LIGHTING THE PATH TO PROFITABLE TRADING
(the whole tutorial handbook pdf Free Download)
A Step-by-Step Guide to Building a Trading
Strategy Verification Tool with VBA Macros
|
|
|
|
