1-Description
The QuoteDonwloader is freeware software to download stock information from Yahoo Finance to a local MySQL database. It reads from a configuration file the indices and downloads automatically the indices components and places the historical information in the database.
The QuoteDownloader is platform independent software developed in Java and tested in Windows and Linux. The QuoteDownloader works as a background process in three different operation ways. The “daily” download is executed daily at 09:00, local time, and downloads all the indices components stock information of the last twenty four (24) hours. The “weekly” download is executed every Saturday at 09:00, local time, and downloads all the indices components stock information of the last seven (7) days. The “full” download is executed in the first time the QuoteDownloader is run in the computer/database and every first Sunday of the month and downloads all the indices components stock information since 1900.
2-Installation
2.1-MySQL Installation
The QuoteDownloader requires the MySQL database to record the indices components stock information. The user is required to install MySQL in a computer with Ethernet connection to the computer where QuoteDownloader is executing. The database and QuoteDownloader execution computer can be the same machine. The MySQL version used in testing was 5.5.15. It should be compatible with newer versions.
MySQL reference manual can be found in http://dev.mysql.com/doc/. The MySQL version 5.5 installation procedures can be found in http://dev.mysql.com/doc/refman/5.5/en/installing.html.
No special installation procedures must be followed with the exception of a user with full permission to the database where the stock information will be placed. In our example here, the user is “mysql”, with the password “mysqlpassword” and the database where the data is stored in MySQL is named “yahoofinance” (the QuoteDownloader software will create the database in MySQL).
2.2-Java Installation
The QuoteDownloader requires the Java Run Environment to be installed in the computer in order to run. The JRE used in the development and testing of the Quotedownloader is jre6. It should be compatible with newer versions.
To download and install JRE please follow the link http://www.java.com/en/download/index.jsp.
2.3-QuoteDownloader Installation
The QuoteDownloader is formed by three different files.
The QuoteDownloader jar file (download here) is where all the binary and library files are placed and is the software that will run to download the stock information to the database. It is not required any update to this file to install and run the software. Download (download here) and place the jar file in a directory of your computer.
The configuration.dtd (download here) is the configuration file scheme to confirm that the configuration.xml is correctly formed. This file does not require to be updated. Download (download here) and place the file in the same directory as the QuoteDownloader jar file.
The configuration.xml (download here) is the configuration file of the QuoteDownloader program. For a MySQL server installed in the same computer with “mysql” user, with “mysqlpassword” password and yahoofinance database, the configuration file does not require to be updated. Download (download here) and place the file in the same directory as the QuoteDownloader jar file.
3-QuoteDownloader Configuration
The QuoteDownloader configuration.xml is made of four configuration blocks. The HTTPConnection configures the QuoteDownloader connection to the Yahoo Finance site. The Quote configures some indentation of quote information from the Yahoo Finance. The DatabaseConfiguration configures the QuoteDownloader connection with the MySQL database. The QuoteIndices configures the Quote downloader indices and components to be downloaded from Yahoo Finance and placed in MySQL database.
<ConfigurationItems>
<HTTPConnection> </HTTPConnection>
<Quote> </Quote>
<DatabaseConfiguration> </DatabaseConfiguration>
<QuotesIndices> </QuotesIndices>
</ConfigurationItems>
The HTTPConnection and Quote will not be required to be updated.
The DatabaseConfiguration defines the MySQL user (User). If the user defined in step 2.1-MySQL Installation is “mysql”, it will require no update. The Password field defines the password for the user. The IP address for the server is defined in DatabaseName. The Port is the mysql port to be used.
<DatabaseConfiguration>
<User>mysql</User>
<Password>mysqlpassword</Password>
<ServerName>localhost</ServerName>
<DatabaseName>yahoofinance</DatabaseName>
<Port>3306</Port>
</DatabaseConfiguration>
The QuoteIndices provides QuoteDownloader with the information of Indices and Components to be downloaded from Yahoo Finance. Quote Indices are organized by world regions with the zone specified, in the case below, the “US” region. Each world region having one or several indice groups, in the case below the DowJones, and one Indice. The Indice is specified by the Indice name (^DJA – Dow Jones Composite Average) and none or several Components, in the case below the AA – Alcoa Inc. Common Stock. The Component is optional. The QuoteDownloader will discover automatically every Component of the Indice.
<QuotesIndices>
<WorldRegion>
<Zone>US</Zone>
<IndicesGroup>
<Group>DowJones</Group>
<Indice>
<Name>^DJA</Name>
<Component>AA</Component>
</Indice>
…
</IndicesGroup>
…
<WorldRegion>
…
<QuotesIndices>
4-QuoteDownloader Execution
After following the steps above and with the three files (QuoteDownloader.jar, configuration.xml and configuration.dtd) in the same directory just type:
java -jar QuoteDownloader-<version>.jar
Or if recognized by the system, just double click the QuoteDownloader.jar file.
The program will start executing automatically, downloading the stock information from the internet, placing it in the MySQL database and producing some logging information.
5-Results
QuoteDownloader stores the stock information in the MySQL database under the database name. The following steps reproduce a configuration identical to the above. If the configuration was updated by the user, just update accordingly.
Connect to MySQL:
mysql –u mysql –p
Enter Password: mysqlpassword
Change to the QuoteDownloader database:
mysql> use yahoofinance;
Show all the tables in the database:
mysql> show tables;
+------------------------+
| Tables_in_yahoofinance |
+------------------------+
| 000020.ks |
| 000040.ks |
| 000050.ks |
| 000060.ks |
| 000080.ks |
| 000100.ks |
| 000120.ks |
| 000140.ks |
| 000150.ks |
| 000180.ks |
| 0001.hk |
| 000210.ks |
| 000220.ks |
| 000230.ks |
| 000240.ks |
| 000270.ks |
| components |
The user can find two types of tables inside the QuoteDownloader database:
1-Several components tables, like the 000020.ks, with the information of the DongwhaPharm stock. With the following structure:
mysql> select * from `000020.ks`;
+----------------+----------+----------+----------+---------+-------------+------------+
| quotedate | open | high | low | close | volume | adjclose |
+----------------+----------+----------+----------+---------+-------------+------------+
| 2000-01-04 | 14100 | 16500 | 14100 | 16500 | 847100 | 462.73 |
| 2000-01-05 | 16800 | 18950 | 15600 | 17500 | 3643800 | 490.77 |
| 2000-01-06 | 17500 | 18400 | 14900 | 15200 | 1303200 | 426.27 |
| 2000-01-07 | 15800 | 17450 | 15000 | 17450 | 2265400 | 489.37 |
| 2000-01-10 | 19300 | 19600 | 16750 | 17000 | 3764400 | 476.75 |
| 2000-01-11 | 17000 | 18500 | 16500 | 17350 | 1505000 | 486.57 |
| 2000-01-12 | 17450 | 19950 | 17000 | 19950 | 3715400 | 559.48 |
1.1-QuoteDate -> Quote date (yyyy-mm-dd)
1.2-Open -> Open value of the stock
1.3-High -> Highest value of the day trade
1.4-Low -> Lowest value of the day trade
1.5-Close -> Close value of the stock
1.6-Volume -> Volume of the stock in the session
1.7-Adjust Close -> Close value adjusted with the dividends and stock split, stock merge, etc.
2-One “components” table with the information of the component. With the following structure:
mysql> select * from components;
+----------------+---------------------------+----------+------------------------+------------------+------------+
| component | componentName | indice | indicegroup | worldregion | todelete |
+----------------+---------------------------+----------+------------------------+------------------+------------+
| AA | Alcoa Inc. Common | ^DJA | DowJones | US | 1 |
| AEP | American Electric | ^DJA | DowJones | US | 1 |
| AES | The AES Corporati | ^DJA | DowJones | US | 1 |
| ALEX | Alexander & Baldw | ^DJA | DowJones | US | 1 |
| AMR | AMR Corporation C | ^DJA | DowJones | US | 1 |
| AXP | American Express | ^DJA | DowJones | US | 1 |
| BA | Boeing Company (T | ^DJA | DowJones | US | 1 |
| BAC | Bank of America C | ^DJA | DowJones | US | 1 |
2.1-component -> Stock component
2.2-componentName -> Name of the stock component
2.3-Indice -> Indice of the stock component
2.4-IndiceGroup -> Indice group of the stock component
2.5-worldRegion -> world region of the indice group
2.6-todelete -> Internal control variable.
In the executing directory the QuoteDownloader produces several log files that report the progress made by the software. The “QuoteDownloaderLogger0.log” file reports the newest log information of the program. This information is important for information and debugging of the application.
6-Notes
The heldersilva.com is the owner of the intellectual property rights which arise from work performed in the com.heldersilva.QuoteDownloader. “Intellectual Property Rights” means all Registered Intellectual Property Rights and all unregistered intellectual property rights granted by law without the need for registration with an authority of office including all rights in information, data, blueprints, plans, diagrams, models, formulae and specifications together with all copyright, unregistered trademarks, design rights, data base rights, topography rights, know-how and trade secrets or equivalent rights or rights of action anywhere in the world.
The heldersilva.com grants the Licensee a non-exclusive license to use all Intellectual Property Rights. The Licensee shall not sublicense any Intellectual Property Rights.
For the avoidance of doubt, any Intellectual Property Rights owned by the heldersilva.com shall remain the property of the heldersilva.com and no representation or act by the licensee (or any sublicense) shall indicate or be construed to provide any other right, title, license or interest.
To the extent allowed by law the heldersilva.com makes no representation and gives no warranty concerning the accuracy, format (either in hard copy or electronic form) suitability of fitness for purpose of information or documentation. The heldersilva.com shall not be liable in any form for any loss, damage or expenses incurred by the Licensee which result from any third party action or any action either directly or indirectly by the Licensee which results from accessing, using, disclosing or copying information or documentation relating to the work. The Licensee shall indemnify and hold harmless the Licensor from any third party claims in this respect.
The heldersilva.com shall have no obligation under this license for corrections of errors or problems of the documentation and software relating to the work.
7-Troubleshooting
For troubleshooting this application please feel free to contact helder.silva at heldersilva dot com. As soon as humanly possible I will try to answer all the questions. Thank you.