Debt to Equity Ratio in Stock Market Browser

Stock Market Browser is a tool that can give you a quick snapshot of the total stock market from sector down to individual stock. One of the financial ratios it provides to you is Debt to Equity ratio. However, the meaning of Debt to Equity ratio in Stock Market Browser is different from the traditional meaning of Debt to Equity ratio. In this article, we are going to use Caterpillar’s most recent balance sheet provided by Yahoo Finance to show the calculation of Debt to Equity ratio in Stock Market Browser and compare the number from traditional Debt to Equity ratio. Following is Caterpillar’s quarterly balance sheet from Yahoo Finance:

Traditional Meaning of Debt to Equity Ratio

Normally, Debt to Equity Ratio is defined a company’s total liabilities divided by a company’s averaged shareholders’ equity

From Caterpillar’s balance sheet of most recent quarter, it has total liabilities 68.09B and averaged total shareholders’ equity (12.883B + 14.162B)/2 = 13.52B. Based on the formula above, its debt to equity ratio would be 68.09 / 13.52 = 5.04
However, this number is different from the number provided by Stock Market Browser:



You can find out Caterpillar (CAT) under sector Conglomerate and industry Conglomerate.
As of 04/02/2012, Debt to Equity ratio provided by Stock Market Browser is 258

Derive Debt to Equity Ratio in Stock Market Browser

First of all, the unit of this ratio is 100%. That means 258 is actually 258%. So how Stock Market Browser derives 2.58?
Instead of using total liability to represent total debt, Stock Market Browser only use item “Long Term Debt” and “Short/Current Long Term Debt” to represent total debt. In other words, it only considers total long term debt as real debt.
Based on Caterpillar’s balance sheet of most recent quarter, it has short/current long term debt 9.648B and long term debt 24.944B. The Debt to Equity Ratio would be (9.648+24.944)/13.52 = 2.58, which is 258%
 

Conclusion

In the traditional definition, the company’s total liabilities is used to represent the total debt, but Stock Market Browser only uses total long term debt, which is the portion of liabilities that accrues the most of interest. Because it only uses the portion of liabilities that accrues the most of interest, the ratio can give investors a better measurement in terms of financial leverage and risk.

Generate Stock Intraday Data for Excel

Not everyone knows that Microsoft Excel has the ability to draw stock chart. If you don’t have professional charting tool, Microsoft Excel stock chart is a simple but useful tool to conduct initial analysis. For example, if you are interested in plotting Coca-Cola (KO) daily chart with 1minute interval, following are the steps to generate stock intraday chart by using data output from Stock Intraday Quotes Download

Step 1: Generate Data Output With Appropriate Settings

Microsoft Excel stock chart requires the data format to be Time-Volume-Open-High-Low-Close, which is different from traditional format, Time-Open-High-Low-Close-Volume. But no problem, Stock Intraday Quotes Download gives you the flexibility to choose the order of data field. Follow is the sample screenshot for the setting in “Customize the data field” window:



Following is the sample screenshot for the setting window:



Make sure the file extension is “csv” so that when you double click the output, it will be automatically opened by Excel. Also make sure the Add Header box is checked. Because we are interested in generating daily chart with interval 1 minute, choose Range to be 1 day and Interval to be 1 min. As for data source, it can be either Google Finance or Yahoo Finance, depends on your preference.
After that, simply add the symbol KO in the table and hit update.

Step2: Load generated data into Excel

Following is a sample screenshot after you open the generated data by Excel



If you are using Microsoft Excel 2010, you can find stock chart type in “Insert”->”Other Charts”->”stock”

Step 3: Generate the Chart

Following is the KO daily chart generated after selecting the column B – G and choose the right most stock chart type



That’s it. You can also use Excel to calculate some indicators such as moving average, RSI, and MACD, and plot them. It’s a tool you can consider and it is free if you already have Excel

ASCII to MetaStock Conversion Utility

 
Free end of day ASCII to MetaStock conversion utility is available now! It allows you to easily convert data generated from Stock Historical Data Download to MetaStock/Computrac format without any charges. Following is a simple instruction:

Step 1: Download the software

You can download the software from eoddata website here. You have to be a registered member to be able to download it. It is free to become a registered member

Step2: Generate stock end-of-day data

You can easily generate stock end-of-day data by using Stock Historical Data Download with appropriate setting:
1. For File Name Setup, the file extension has to be set as “txt” and the postfix has be set as “_date”. For example, if your end-of-day data is generated in 2012 March 24, type “_20120324” here
2. For Format Setup, the Date Format has to be set as “YYYYMMDD” and the Separator has to be set to none
Following is a sample screenshot of the necessary settings.



Following is the sample screenshot for MetaStock column 8 setting nn the “Customize the Data Field”

Step 3: Convert EOD data into MetaStock Format

After generating EOD data, we are ready to convert it into MetaStock format. First, launch the conversion utility. Following is a sample screenshot that you will see after launching the conversion utility:



The setting is very straightforward. Simply choose your source directory and destination directory, and then hit the “Convert” button.

PE and PEG Ratio

PE ratio probably is one of the most wildly used ratios for investors to make the investment decision. The reason might be because it is easy to understand and it simplifies the decision making. However, it might be misleading by only looking at the PE ratio

What is PE Ratio?

PE ratio stands for price-to-earnings ratio. It is the current stock price divided by the annual net income (earnings) per share (EPS). For example, if stock XYZ is trading at $20 and its annual earnings per share is $2, the PE ratio would be $20/$2 = 10

Implication of PE Ratio

PE Ratio is usually used to compare to the yield of other investment alternatives. From investors’ point of view, when they buy a stock with PE ratio 10, it can be treated that this investment has annual return on investment 10% (you paid $20 for a stock that can earn $2 in a year). With that mindset, you can compare to other investment alternatives to determine the relative attractiveness. (Remember the article Market Risk Premium 101?)

You can also compare the stock’s current PE ratio to its historical PE ratio to get a sense whether the stock is relatively cheap at this point of time. For example, if stock XYZ was trading at PE 100 last year but is currently trading at PE 10, it seems this stock is relatively cheap at this point of time

Can’t Make Investment Decision by PE Alone

However, there are traps here by making investment decision purely using PE ratio:
1. Stock price fluctuates all the time: Even though we say that PE ratio 10 can be treated the same as 10% annual yield, there is no guarantee the stock price won’t go down. It is possible that stock XYZ is trading at $20 now with PE ratio 10 but goes down to $10 after one year. Besides that, depends on the company policy, you don’t really benefit immediately from $2 earnings if the company doesn’t pay any dividend

2. Earnings fluctuate year over year: Even though a stock might seem to be expensive at this point of time, that doesn’t automatically make it a poor buy. The trap here is that earnings will change year over year. For example: if stock XYZ has average PE ratio 10 historically and is currently trading at $20 with past 12 month earnings $1, its PE ratio is 20. It seems to be expensive now. Suppose this company is doing very well for the next year so that after one year, its past 12 month earnings increased to $2.5. If stock price is still trading $20, its PE ratio changes to 8. If we have crystal ball to tell us the company XYZ is going to have earnings $2.5 for the next year, that will make this stock’s current trading price ($20) a good buy. Unfortunately, no one has crystal ball.

Important of the Expectation of Earnings Growth Rate

From the above example, not only PE ratio but also the expectation of the future earnings growth rate will affect the stock price. If investors believe that the company XYZ is going to have high earnings growth rate, they might be willing to pay higher price to own its stock even that means buy at high PE ratio. On the other hand, if investors believe that the company XYZ is going to have slow or negative earnings growth rate, PE ratio of that company’s stock tends to be low. As you can see, because no one has crystal ball, it is the “expectation”, not the “real”, future earnings growth rate that drive the stock price up and down.

Introduction to PEG Ratio

PEG ratio of a particular stock is its PE ratio divided by its expected annual EPS growth rate, as a percentage. The rationale behind this calculation is that if stock is trading at high PE, it is expected to have high EPS growth rate, and vice versa. For example: if stock XYZ currently is trading at PE ratio 10 and its expected EPS growth rate is 10%, the PEG ratio of stock XYZ is 10/10 = 1. Because PEG ratio take stock’s annual EPS growth rate into consideration, many investors use PEG ratio to determine if a stock is under or overvalued. The lower the PEG ratio, the better (more undervalued) the stock is (same PE with high EPS growth rate or same EPS growth rate with low PE).

In Practice

We can use Stock Fundamental Data Download to see the current PEG ratio distribution of companies listed in Down Jones. The PEG ratio number is extracted from Yahoo Finance. It is based on the expectation of the next 5 years growth rate. Following is the result sorted by PEG Ratio.



As of today (02/16/2012) GM has the lowest PEG ratio (0.41) while T has the highest PEG ratio (3.39) among Down Jones stocks. If you pay attention to the dividend yield, you see that in general companies have low PEG ratio pays little dividends compared to companies have high PEG ratio. It is obvious that there is always a tradeoff in terms of investment. The final decisions really depend on your ultimate goal, which could be different among individuals.

Let us emphasize one more time, No matter what the data source is, the annual EPS growth rate you see is the “expected” number because no one can foresee what will happen in the future. That’s the reason why you will see the dramatic price movement of some stocks after the earning is announced.

Ex-Dividend Date and Dividend Pay Date

In the previous article Introduction to Dividend Yield, we talked about several investment strategies that are based on dividend yield. Since part of the strategies are based on the amount of dividend you receives, it is important to know the difference between stock ex-dividend date and dividend pay date. the reason is that because stocks are constantly traded and hence the ownerships are constantly changed. Joe bought stock XYZ in Jan, 1 and sold it in Feb, 1 to Mary. In Mar, 1, Mary sold it to Peter. If stock XYZ is going to pay the dividend in April, 1, Who is in title to receive the dividend?

What is Ex-Dividend Date

The ex-dividend date sometimes is called the reinvestment date.  It is the date to determine if you will be in title to receive the dividend of the stock you own. The official definition from IRS is “the first date following the declaration of a dividend on which the buyer of a stock is not entitled to receive the next dividend payment”. Probably you are still confused. Simply put, it means that if want to be in title to receive the dividend of the stock XYZ, you have to buy this stock before ex-dividend date. If you buy the stock XYZ on or after ex-dividend date, you won’t be in title to receive the dividend.

What is Dividend Pay Date

In terms of dividend pay date, it is much straightforward. It is the date that dividend is actually paid to the stock owner that are in title to receive the dividend

In Practice

In the previous article Introduction to Dividend Yield, we mentioned about the strategy that invests in stocks that have the highest dividend yield. By using Stock Fundamental Data Download, we can sort the stock by its dividend yield as following



So currently stock T (AT&T) has the highest dividend yield among Dow Jones stocks with Ex-Dividend Date Jan, 6 and Dividend Pay Date Feb, 1. That means if you want to be in title to receive the next dividend payment paid by AT&T in Feb, 1, you have to buy the stock before Jan, 6
You might think about buy AT&T stock in Jan, 5 then sell in Jan, 6. In that way, you can receive the next dividend simply by holding the stock one day. However, because it is public information and everyone knows about it, the stock price already factors in this information so when you try to sell AT&T stock in Jan, 6. the stock price should have already reduced by the dividend amount that is going to be paid in Feb, 1

Introduction to Dividend Yield

Simply put, dividend yield is the amount of annual dividends per share divided by the stock price per share. For example, if a company pays annual dividend $5 and currently the company’s stock is trading at $50, the dividend yield is 5/50 = 10%. Many investors prefer high dividend yield stock rather than high growth stock without dividend payment. The reason is because in general it seems to be safe to hold high dividend yield stock especially when the outlook of the economy is uncertain. Think about it: The only way to profit by investing in the stock that doesn’t pay the dividend is through the stock price appreciation. However, investors can profit from high dividend yield stock simply by receiving the dividends. It is very attractive when everyone thinks the economy won’t be good in the near future.

Dividend Yield Strategies

There are several investment strategies that we can consider by applying dividend yield concept

Dogs of the Dow

Dogs of the Down is a simple strategy that suitable for investors that prefer high dividend yield with passive investment style. Here is how it works: At the beginning of the year, you choose the top 10 highest dividend yield stock in Dow Jones and invest the equal amount of money into each one. By the end of the year you liquidate it and repeat the same process for the next year
You can find the list of Dogs of the Dow for 2012 here
Also you can use the Stock Fundamental Data Download to list the stock from highest dividend yield to lowest dividend yield among Dow Jones stock.



You can also include the ex-dividend date and dividend pay date information. The list is not the same as the list for Dogs of the Down 2012 because it is based on the latest trading data as of Jan, 31, 2012
 

Vertical Sorting of Dividend Yield

Another way is to sort the dividend yield by the segment. Take SPY, which is the S&P 500 index ETF for example, we can dissect SPY into following segment: XLE (Energy), XLF (Finance), XLI (Industry), XLK (Technology), XLU (Utility), XLP (Consumer Staples), XLV (Health Care), XLY (Consumer Discretionary).  Simply load those symbols into Stock Fundamental Data Download and sort them by the dividend yield. Result is as following:



The result shows XLU (utility) has the highest dividend yield and XLK (technology) has the lowest dividend yield

Horizontal Sorting of Dividend Yield

You can also sort the dividend yield horizontally. For example, you might be interested in investing in different countries and wonder what the dividend yield is for each of the country.
Here is an example that we can sort by dividend yield among following 19 countries.



As you can see, among the listed countries, EWP (MSCI Span Index) has the highest dividend yield (9.47%), almost 10%! This high dividend yield reflect the fact that currently investors are not confident in regarding the current debt issue Span is facing

www.analyxit.com
support@analyxit.com

Stock Fundamental Data Download Available Now

We are pleased to announce that the Stock Fundamental Data Download (SFDD) version 1.0 is released! Wondering which stock you are interested in has the lowest PE ratio? How about PB and PS ratio? Which stock has highest earning growth rate potential? Stock Fundamental Data Download provides the solution for you!

Stock Fundamental Data Download (SFDD) can download the fundamental data for the stock symbols that are listed in Yahoo Finance and save it as .csv format. You are able to create your own symbol list and define the data fields you would like to download (currently there are more than 30 data fields supported and more to come). Also SFDD allows you to edit and sort the data downloaded on the fly before you export the data. You can access the complete document here.
 

Main Features

· Only one time purchase fee, no further subscription fee
· Free upgrade for future release
· Batch downloads the symbol lists defined by you.
· Support more than 30 data fields, and more is coming
· In-software edit capability allows you to rearrange the data before exporting it
· Support various stock exchanges
· Define your own data field to download
· output data is plain ASCII text with comma separated csv format

We provide the trial version with absolutely no cost to you. Try it now and tell us what you think. We would be more than happy to get any feedback or suggestion from you!

www.analyxit.com
support@analyxit.com

New Version of Stock Historical Data Download Available Now

We are pleased to announce that the Stock Historical Data Download version 1.6 is released! If you have purchased this software before, you can upgrade to the latest version simply by login to your account and reinstall the file. New features in the latest version includes the integration of the command mode and the capability to save/load .set file directly from Setting Window

Integration of the Command Mode

Stock Historical Data Download supports command line argument to achieve the fully automation. It is done by reading the symbol list file (.slist), which includes the symbols going to be downloaded, and the setting file (.set), which includes the setting options that are going to be loaded to setting window.

Create/Load Symbol List file

We recommend you to create/load symbol list file (.slist) by following steps:
1. Add the symbols you want to save in the .slist file, then chose File->Save File->Symbol List from main window to save the list into the file
2. To load the saved .slist file, simply choose the file from File->Open->Symbol List from main window

Create/Load Setting File

We recommend you to create/load setting file by following step:
1. In the setting window, choose the setting you would like to save, then click OK
2. In the main window, choose File->Save File-> Setting to save the current setting into .set setting file. Note the data field information will also be saved
3. To load the saved .set file, simply choose the file from File->Open->Setting

Use the Command mode

Stock Historical Data Download supports the command mode by directly activating the exe file “StockDataDownload.exe” from the Windows console. Following is the sample screenshot of the command mode window:



You can find the executable from the installation directory. The command mode has the general format:
StockDataDownload.exe -s “slist file” -o “setting file” [-q]
“slist file” is the target symbol list file.
“setting file” is the predefined setting file
if –q option is specified, the command mode window will be automatically exist when the task is done otherwise you have to hit the Quit button to exist the window
You have to specify both files to make the command mode work

Directly Save/Load .set file from Setting Window

Following is the sample screenshot of the Setting Window for version 1.6



At the Bottom of the window there are Save/Load buttons that allows you to directly save/load .set file from the Setting Window. It is more convenient!



www.analyxit.com
support@analyxit.com

Generate MetaStock ASCII Format by Stock Historical Data Download (SHDD)

Introduction

MetaStock is a stock charting and technical analysis tools created by Equis International. In order to use this tool, there are two necessary components: The MetaStock software itself and the data service. However, data service subscription is not cheap. If you are MetaStock End-Of-Day user, it costs you $59/month for only North American Subscription Package. Another feasible option would be purchasing MetaStock End-Of-Day software and using SHDD to provide you the data.

How it works

it is simple. First, you use SHDD to generate MetaStock ASCII format, and then use the tool DownLoader provided by MetaStock software to covert ASCII to final MetaStock format.

What is MetaStock ASCII Format

Depends on the version of the MetaStock software you are using, there are different ASCII format:

1. MetaStock ASCII (7 column): The sample format will look like this:

INTC,20111212,24.18,24.29,23.61,24,94839500

INTC,20111213,24.06,24.06,23.42,23.56,78518900

INTC,20111214,23.48,23.56,23.14,23.31,56394300

Column 1: Symbol

Column 2: Date

Column 3: Open

Column 4: High

Column 5: Low

Column 6: Close

Column 7: Volume

2. MetaStock ASCII (8 column): The sample format will look like this:

INTC,D,20111212,24.18,24.29,23.61,24,94839500

INTC,D,20111213,24.06,24.06,23.42,23.56,78518900

INTC,D,20111214,23.48,23.56,23.14,23.31,56394300

As you can see, it is similar to ASCII (7 column) format. The only difference is that there is an extra column to represent the bar duration. It is usually D for day.

Settings to Generate MetaStock ASCII Format

For the complete doc about Stock Historical Data Download, please click here.

You can achieve this easily by editing the setting window:

1. MetaStock ASCII (7 column)

a. Set the Date Format as following:



You can check the Add Header if you want the header to be in the data

b. Customize the Data Field:



You can save the current setting and load it next time to save the time

2. MetaStock ASCII (8 column): It is similar to MetaStock ASCII (7 column) setting with following column setting:



That’s it!

As of writing, the most recent version of SHDD is 1.5. Please always use the latest version.

Actually not only MetaStock format, but virtually any format can be generated by SHDD as long as the total columns are less than 8. If you have any specific format that would like us to support, please let us know and we will implement it for you for the next release.

www.analyxit.com

support@analyxit.com