1. Guide To Excel For Finance: Introduction
  2. Guide To Excel For Finance: Linking Yahoo! Finance and Other Outside Financial Data To Excel
  3. Guide To Excel For Finance: PV And FV Functions
  4. Guide to Excel for Finance: Annuities
  5. Guide To Excel For Finance: Valuation Methods
  6. Guide To Excel For Finance: Goal Seek
  7. Guide To Excel For Finance: HLookup And VLookup
  8. Guide To Excel For Finance: Ratios
  9. Guide To Excel For Finance: Technical Indicators
  10. Guide To Excel For Finance: Advanced Calculations
  11. Guide To Excel For Finance: Conclusion

There are a number of ways to link external stock market and financial data into Excel. The point of downloading data is to be able to customize and manipulate it to create your own formulas, which can be refreshed throughout the day as stocks change in value, or as often as you would like to refresh the steady stream of market information coming in.  This can save a step where data entry is no longer needed, freeing up time to manipulate and interpret financial information.  (Related: Stocks Basics: How to Read A Stock Table/Quote)

Yahoo! Finance offers the capability to download basic stock price information into Excel. For users who have created their own portfolios, in Yahoo! Finance there is a link or "Download" function to save the information into an Excel workbook. Data that can currently be downloaded includes current stock prices, the daily high and low prices and daily trading volume.

Additionally, it is possible to download just about any data from the Internet into Excel. In Excel under the "Data" menu, choose to download data from the web or "From Web," which is the specific menu item in Excel. A separate pop-up will open and allow the input of literally any website. Returning to Yahoo! Finance and the "Statistics" portion for AMC Networks.

 
 

Below is the specific table that Excel created:

 

Users may also want to manually calculate ratios to have a more customized experience. For instance, the specific ratio the user needs may not be calculated by Yahoo! Finance, or the user might want to use different time periods in the calculations. For instance, using the trailing twelve month data versus the last full fiscal year of information can help provide more current ratios to analyze. Below are other examples of specific ratios and how to calculate them.

Once basic company financial information has been downloaded, custom ratios and analyses can be performed.  Below is a sample spreadsheet where company information has been downloaded from www.sec.gov, the Securities and Exchange commission site that contains originally company filings.

Formulas below include the price to earnings ratio, or the stock price divided by earnings.  Earnings yield is simply the inverse of the P/E ratio and, when compared to bond and Treasury yields, provides an indication of whether a stock might be over or undervalued.

Price to free cash flow (P/FCF) per share looks at a firm’s cash flows, which can be used in a discounted cash flow (DCF) analysis to value a stock. We will discuss DCF in further detail below.  FCF Yield is again the inverse of P/FCF.  Return on assets (ROA) is net income divided by a firm’s total assets.  Return on equity (ROE) uses shareholders’ equity in the denominator, and return on capital (ROC) uses a firm’s total capital (shareholders’ equity plus long-term debt) in the denominator.  These are measures of how effectively management is utilizing company capital to generate returns for shareholders. 

 


Guide To Excel For Finance: PV And FV Functions
Related Articles
  1. Investing

    Guide To Excel For Finance

    Formulas, functions and features you need to know when using Excel for financial analysis.
  2. Insights

    Buyers Want Yahoo Japan, Not Yahoo

    Buyers may be more interested in Yahoo’s Japanese counterpart than the company itself.
  3. Investing

    Tracking Your Portfolio On Yahoo! Finance

    Yahoo! Finance can sync with brokerage accounts, merge and track investments to monitor performance and help you understand where investment risks lie.
  4. Investing

    Yahoo! Stock: An Earnings Case Study (YHOO)

    Discover the earnings analysis about Yahoo!, including comments on historical earnings and the breakdown of analysts' past and future earnings estimates.
  5. Investing

    Yahoo: New Data Breach Involved Forged Cookies

    Yahoo disclosed another potential hack, this time in 2015 and 2016 thanks to forged cookies.
  6. Small Business

    Yahoo Stock: Analyzing 5 Key Customers (YHOO)

    Read about a handful of technology companies that serve as key customers for advertising space on the Yahoo family of Web properties.
  7. Investing

    Analyzing Yahoo's Return on Equity (YHOO)

    Learn about Yahoo's return on equity (ROE) and find out how net margin, asset turnover and financial leverage impact its ROE relative to industry peers.
  8. Investing

    Before Verizon, Japan Unit Sought Yahoo Buy (VZ, YHOO)

    Yahoo Japan proposed a merger with U.S.-based Yahoo Inc. right before it agreed to the $4.8 billion deal with Verizon.
Frequently Asked Questions
  1. How Do Real Estate Agents Get Listings?

    Learn about how real estate agents get listings in top real estate markets, as well as in smaller areas, through utilizing ...
  2. How Do I Use Software to Make Arbitrage Trades?

    Understand the meaning of arbitrage trading, and find out how traders leverage software programs to detect arbitrage trade ...
  3. What is the difference between market capitalization and market value?

    Understand the difference between market capitalization and market value, including the elements used for the calculation ...
  4. How is an accrued interest entry made in accounting?

    Learn how to create common journal entries for accrued interest, including adjusting entries and delayed bond issues sold ...
Trading Center