Analyze stock market data from the Web in Excel

Do you want to analyze data from the Web (such as the latest stock quotes) in Excel 2000? Would you like to update the worksheet with the latest information with the click of a button? You can do this with the Web query feature.

Try a sample Web query

Sample Web query files are included with Excel 2000 to help you get started. For instance, use the Microsoft Investor Stock Quotes Web query to see the latest stock quotes from MSN™ MoneyCentral™ Investor. To use this sample query, follow these steps:

1. Be sure you have access and are connected to the World Wide Web.
2. Open a new workbook in Excel.
3. On the Data menu, click Get External Data, and then click Run Saved Query.
4. In the file list in the Run Query dialog box, click Microsoft Investor Stock Quotes.iqy.
5. Click Get Data.
6. In the Returning Data to Microsoft Excel dialog box, click OK.
7. In the Enter Parameter dialog box, type the stock symbol for which you want to return a quote. For example, for Microsoft, type msft. Click OK.

OBTAIN ADDITIONAL SAMPLE QUERIES

You can obtain a variety of additional sample queries by clicking Get More Web Queries.iqy in the Run Query dialog box. This is a query that downloads information and hyperlinks to additional sample Web query files from Microsoft.

Create your own Web query

Following the procedure above should give you an idea of how Web queries work. Want to create your own Web query?

First, it's a good idea to have in mind the Web site from which you want to get data, and to have the address of that Web site. Then, open the workbook with the worksheet where you want to display the data. When you're ready, follow these steps:

1. On the Data menu in Excel, point to Get External Data, and then click New Web Query.
2. In the first section of the New Web Query dialog box, type or paste an address for the Web page. Or, if you don't have the address, click theBrowse button to start your browser, locate the page you want, and switch back to Excel using ALT+TAB. The address will be automatically filled in for you.
3. In the second section of the New Web Query dialog box, choose the data you want returned:
• The entire page returns text, tables and other data on a page (does not include graphic images). Choose this option when the page contains just the data you want and does not include advertisements, banners, navigation buttons, or other elements that may clutter the worksheet or that Excel may not be able to interpret properly.
• Only the tables returns all tables or preformatted (

  ) sections on the page. Use this option when you do not want to import advertisements, banners, navigation, or other elements on a page.

• One or more specific tables on a page returns only the table(s) that you specify. Use this option when you want data out of a specific table or tables only.

To specify which tables to return

4. In the third section of the New Web Query dialog box, choose the type of formatting you want returned:
• None returns plain text without formatting such as font face or color.
• Rich text formatting returns most Web page formatting such as font face and color, but does not return hyperlinks or other types of advanced formatting.
• Full HTML formatting returns all of the Web page formatting that Excel supports, such as hyperlinks.
5. Click OK.
6. In the Returning Data to Microsoft Excel dialog box, specify where you want to put the data (starting in a cell you specify on an existing worksheet or, in a new worksheet). For more advanced options such as refresh control and query definition, click Properties. For information on the options in these dialog boxes, click the Help button in the upper-right corner of the dialog box, and then click the option for which you want help.

UPDATE THE DATA

Later, to update the data to match the latest information in the Web page, click theRefresh Data button on the External Data toolbar that is enabled when you click a cell within the queried data. To cancel the query, click Stop Refresh.

MODIFY THE QUERY

If you want to change the type of data or formatting that your query returns, click a cell within the queried data, and then click the Edit Query button on the External Data toolbar.

You can also change some properties of the query before you run it. For example, if you want to change the query so that it is refreshed automatically each time you open the workbook, click Properties in the Returning Data to Microsoft Exceldialog box, displayed after you click Get Query in the Run Query dialog box.

SPECIAL HANDLING

Click the Advanced button if the page you are importing contains preformatted (

) sections that you want handled in a certain way, or when there are numbers on the page that could be mistaken for dates (for example, a part number such as 00-01-45). For information on an option, click the Help button in the upper-right corner of the dialog box, and then click the option for which you want help.
SAVE YOUR QUERY
When you click OK in the New Web Query or Edit Web Query dialog box, a query is created and stored in the workbook so that data can be updated later. To save the query in a separate file so that you can use it with other workbooks or share it with other people, click the Save Query button. Type a file name in the File name box, and click OK. By default, the file is saved as a text file with an *.iqy extension in the \Windows\Application Data\Microsoft\Queries folder. This location provides the easiest access to the file from the Run Query dialog box, which is displayed when you click Run Saved Query on the Get External Data menu (Data menu). To share the file, simply save it on another user's machine.
To run a saved query, click Get External Data on the Data menu, and then clickRun Saved Query and choose the query you want to run in the Run Query dialog box.
After the saved query is run in a workbook for the first time, the information to update the query is stored in the workbook. Therefore, if you make modifications to the query in the Edit Web Query dialog box, the modified information will be stored in the workbook rather than in the *.iqy file. To update the *.iqy file as well, clickSave Query.
Analyze the data
Use Excel's powerful analysis tools and formulas to analyze your data. For instance, if you're calculating stock option amounts, you can create a formula that determines net profit by calculating the market price of the stock minus the purchase amount. Instead of using the actual stock price in the formula, use a reference to the cell that contains the latest stock price. That way, when you update the data, the formula will recalculate to show the latest profit amount.
More information
For more information on Web queries, type Web Query in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

Trailing Stop Orders

A trailing stop order can let you protect profits. As the stock price goes up, you can tell your broker to keep trailing it and only sell if it falls, say, $2 from its highest price ever. At that point, the order gets converted to a market order.

One way to protect gains and limit losses automatically is by placing a trailing stop order. With a trailing stop order, you set a stop price as either a spread in points or a percentage of current market value.

Imagine you purchased 500 shares of Hershey Chocolate at $50 per share; the current price is $57. You want to lock-in at least $5 of the per share profit you’ve made but wish to continue holding the stock, hoping to benefit from any further increases. To meet your objective, you could place a trailing stop order with a stop value of $2 per share.

In practical terms, here’s what happens: Your order will sit on your broker’s books and automatically adjust upwards as the price of Hershey’s common stock increases. At the time your trailing stop order was placed, your broker knows to sell HSY if the price falls below $55 ($57 current market price - $2 trailing stop loss = $55 sale price). Imagine Hershey increases steadily to $62 per share; now, your trailing stop order has automatically kept pace and will guarantee at least a $60 sale price ($62 current stock price - $2 trailing stop value = $60 per share sale price). In other words, the trailing stop order will increase in your favor and lock in any gains you’ve made in the interim. If Hershey were to fall to $60, your trailing stop order would convert to a market order for execution, your shares would be sold, and should result in a capital gain of $10 per share.

Using Trailing Stops to Protect Stock Profits

In another article, I discussed the value of stop loss orders as insurance for the market moving against your stock. Trailing stops, a form of stop loss orders, can also protect a profit and, if you’re clever, follow a stock’s rising price. Let me explain.

First, a quick review. A stop loss order placed with your broker is a way to protect yourself from a loss, should the stock fall. The stop loss order tells your broker to sell the stock when, and if, the stock falls to a certain price.

When the stock hits this price, the stop loss order becomes a market order. A market order instructs your broker to sell immediately at the best possible price. In a volatile market, you may not get the price you wanted, but it should be close.

Protect Your Profits

That’s how you protect yourself from a bad loss. Now, here’s how you use the stop loss order to protect your profit on a stock that’s rising.

There are two ways to enter a stop loss order. You can enter a dollar amount, for example if your stock is selling at $40 per share, you might enter a stop loss order for $37.50 per share. When the stock price drops to $37.50, it trips the stop loss order and the broker sells it.

However, what if you were fortunate enough to (through careful research) to have a growth stock that was rising on a fairly steady basis? Let me set up a scenario.

You bought the stock two years ago for $25 per share and it has grown 23% each year and is now pushing $38 per share. When you can stop patting yourself on the back, you began to get a little nervous that this run of growth might be coming to an end.

Headed for a Fall

The P/E or Price to Earnings Ratio is higher than at any point in the past three years making you think that the stock is overvalued and due for a fall.

You could take your profits and run, but what if the stock still has some legs and there’s more growth ahead? On the other hand, if it takes a fall, you stand to lose some of your handsome profit.

Here’s where the stop loss order bridges the gap and gives you an alternative that keeps you in the stock, but protects you profit.

You simply give your broker a stop loss order called a trailing stop, which is a percentage below the market price. For example, you might tell your broker you want a trailing stop 10% below the market price.

Trailing Stop

Using our example, the trailing stop would kick in at $34.20 per share ($38 x 10% = $3.80; $38 - $3.80 = $34.20).

If the stock keeps moving up, so will the trailing stop. For example:

• At $39 per share, the trailing stop is $35.51
• At $40 per share, the trailing stop is $36.00
• At $41 per share, the trailing stop is $36.90
• At $42 per share, the trailing stop is $37.80

As long as the stock keeps rising or holds relatively steady, nothing happens. However, if it turns south and hits your trailing stop, your broker sells and you pocket your profit. It is important to note, the trailing stop only goes up, it never goes down with a market price.

The trick is setting the percentage at a level that will pick up a true price drop as opposed to normal daily price fluctuations.

Conclusion

Several trading techniques use trailing stops. This example is a simple strategy to protect your profit. More advanced traders use it in combination with other maneuvers to extend their advantage. However, this strategy works just fine for beginning to intermediate investors who want to protect a profit, but let a winner run as long as possible.

Advantages and Disadvantages of Investing in Gold Bullion vs. Gold Stocks vs. Gold ETFs

Most analysts agree that a well-balanced portfolio should allot a certain amount, somewhere between 5-15%, to gold, depending on your outlook for the economy in the near, mid, and long-term future. One decision you need to make is how to allocate this portion amongst the several options for owning gold. You can invest in the physical yellow metal, buy gold mining equities, or buy gold ETFs.

Let’s look at some of the basic strategies for each of these types of gold investing.

Investing in Physical Gold Bullion

Amazingly, there are several ways you can invest in physical gold. Most people imagine hoarding the physical coins and bars themselves, maybe burying these in your backyard, but you can invest in bullion through “pooled” accounts (a bit like mutual funds), and you can also invest in physical gold directly without ever needing to store it yourself.

Buying physical gold and storing it yourself.

This is the most tangible option and might give the most feeling of security. If the world’s banking system collapses, it’s nice to know you’ve got some gold coins at home (or in a safety deposit box). The downside to keeping it yourself is theft and liquidity. You need to have the gold delivered to your home, you need to store it privately, and it also means that if you ever decide you need that money in a hurry later on, it might take a few days to be able to sell your gold. Also, you might want to spend money to insure it.

Investing in pooled accounts.

You can buy gold through a dealer like Kitco or the Perth Mint, own your exact allotted amount, but the gold itself will be fungible. This means that there is no specific gold barwith your name on it. Rather, an audited amount of gold is sitting around in the dealer’s storage facilities and the gold you “own” is really a promise to pay you back that amount of money once you decide to sell it again. You can, of course, opt to take delivery of your gold, at which point you would become a physical buyer.

Buy physical gold directly, but don’t take storage.

A variation and “middle way” between the above two options, you can buy gold most directly through a dealer like GoldMoney, which is basically a gold bank. GoldMoney does not use pooled accounts – you will actually have a gold bar there with “your name” on it, so to speak. Their vaults are audited regularly and if you buy a half ounce they will physically add a half ounce to their vaults for you. The advantage here is that they pay for the storage and insurance. There is also a considerable degree of liquidity, since, as a bank of sorts, GoldMoney gold is instantly convertible back into one of four currencies. The downside to this option is that there is a monthly fee for holding your gold, and this small amount gets deducted from your total goldgrams balance. Theoretically, then, if you let your account sit long enough, the balance could go down to zero (this would take an extremely long time, depending on the price of gold).

Top Gold and Silver Dealers – Invest Directly In Bars and Coins

Thus, the advantages of having your own gold bullion (bars or coins) is obvious. The disadvantages are security and liquidity, as well as having to pay fees for insuring your investment in one way or another. From an investment worth point of view, this is the purest way to play the metal as a commodity. When gold commodities and futures go up, the market worth of your gold bullion goes up. Gold is one of the few commodities in which it is possible to invest so directly as this.

Investing in Gold Explorers, Gold Miners, and other Gold Stocks

If you don’t want to deal with storage, insurance, or paying monthly account fees, investing in gold equities can be a much easier option. You’ve already got your brokerage account set up, you just need to purchase the stocks. Some of these gold stocks pay dividends, too.

Advantages of Gold Stocks

Market diversification. Gold equities don’t necessarily move in lockstep with the commodity. For various market-related reasons, the stock of Barrick (TSX: ABX) or Agnico-Eagle Mines (TSX: AEM) might be over or under-valued in relation to what the metal itself is doing. This may be disappointing when bullion is rising to new highs, but it can also counteract the effects of weaker gold prices when it happens. During the Great Depression, gold stocks soared, even when physical gold became illegal to own privately.

Asset diversification. In addition, many gold producers, like BHP Billiton, also produce a signficant number of other precious and common metals, and this can provide some more cushion to the stock and hedge against gold prices. It also means, however, that the stock will be open to risks coming in from other directions related to those metals, too.

Liquidity. Stocks can be relatively easy to get in and out of. If you need to sell on the open market, it’s much easier than having to find and contact a local dealer.

Gold Dividends: 11 Mining Stocks That Pay Them
Top 20 List of the Largest Canadian Gold Stocks

Disadvantages to Owning Gold Stocks

Broad Market cycles. It’s hard to say which would be the more potential victim to gold speculation, bullion or the stocks. But gold equities will also get caught up in general market forces such as bull and bear cycles that may not affect gold prices themselves as much. Gold stocks might act differently because they are equities, yet intricately linked to commodities, which are the most volatile asset class. Take a look at Barrick’s recent and very unique situation.

Company Risk. Because you’re dealing with individual companies, you’ve got management risk. A change in management, or management decisions, a lawsuit, political risk, local currency risk and other factors can all affect the valuation of the company itself and hence, the value of your stock.

Investing in Gold ETFs – Best Way To Go?

Gold ETFs might seem like the perfect common ground between the above two options. But don’t forget that there are different kinds of gold ETFs. Some trade gold futures. Others, more recently, can hold the physical bullion itself. Most exotically, there are also leveraged gold ETFs. These are intended for very experienced investors. If you are new to investing, let alone investing in gold, please do not start with leveraged gold ETFs! They might be (or, I should say, seem) simple to understand, but so is a sharp 10″ chopping knife.

Advantages of Holding Gold Bullion ETFs.

Depending on the type of ETF, you get the benefit of either asset form listed above – your own metal, safe storage, and liquidity. You also don’t have to pay insurance or account fees. You might have more direct exposure to the commodity if your ETF, like GLD, holds the bullion itself in vaults. On the other hand, if you prefer owning the equities, and you want a dividend of some sort, then an equity ETF is the way to go and really does provide unique value – maximum diversification with a tiny bit of income, too.

Disadvantages of Holding Gold Bullion ETFs.

There are still fees that eat up your costs here – the ETF management fee, as well as commission fees for each buy and sell. There is the market risk of the underlying companies, and depending on how actively managed your ETF is — some are actively managed, yes — there may be some management risk or turnover risk. You must check out the specific ETF you are interested in and research its own exposures. I’ve provided a list of the common gold ETFs in a previous post.

My take: if you’re a beginner, or new to investing in gold, at this point I would probably recommend an ETF, although you will have to determine whether it would invest in a gold index (such as iShares XGD) or bullion itself (such as GLD). Because the gold companies can be so subject to cyclical forces as well as production forces, it makes sense to spread your risk out among a number of them. Once you become more familiar with the landscape and get to know individual companies you might want to consider investing in a particular company. I’ve made a list of the top 20 most-traded Canadian gold stocks as a resource. It also lists some of the more common gold ETFs.

Mutual funds are an option too, although they are less and less what I would recommend due to fees for management and other costs. If you can find a mutual fund with an MER comparable to its closest ETF, it might be worth considering.

Whichever asset group you choose, it’s smart to have at least some exposure to gold – it’s not just an investment, it’s a form of insurance, too. Like having an emergency fund, gold can help insulate your portfolio from market shocks like inflation, since gold is negatively correlated with various sector movements. Just be sure to do your due diligence before making your final decision.

Gold Investments: Men or Metal?

The Differences between Owning Stocks and Owning Metal

This would be a good time to review some of the differences between gold stock ownership and owning the metal itself given that a large number of people are now interested in the gold universe. Simultaneously, inflationary fires are being fanned globally by the twin threats of rising oil prices and competitive de facto currency devaluations which only throw fuel on that fire. These fundamental developments will add more potential gold owners to our growing ranks. It is important for investors to make the right decision -- and to be certain that the gold item they own is the one which will serve their purposes. Choices need to be made...

I've worked with a great many gold investors over the years. They fall into two distinct groups:

Savers and speculators.

Occasionally, an investor will combine the two at some level, but savers tend to emphasize owning the metal outright, and speculators tend to emphasize stock ownership -- an 80%-20% split in either classification is not uncommon, if not 100% ownership of one or the other. My purpose with this short essay is not to convince anyone on the merits of gold ownership. This is addressed to those who have already made the decision to own gold and are trying to decide how to allocate funds within their portfolios.

Those hedging the negative financial or economic event own the physical metal in the form of coins and bullion. It would be hard to imagine an individual concerned about a breakdown of some sort saying "I think the system is going to fail. I'm going to go out and buy some gold stock." Doesn't make much sense, if you get where I'm going with this.

In recent years, the majority of our clientele has fallen into the "saver" category -- those who simply wish to preserve what they've garnered no matter what the government (or central bank) does to our money. Speculation, by and large, runs against their grain.

Others, those who believe that they can beat the system by being quick on their feet, purchase gold stocks. Sometimes they win. Sometimes they lose. Most, though, are speculators looking for a return -- not savers looking to protect themselves.

Most important: Stocks are stocks first and gold second. They are not (I repeat NOT) a substitute for the metal.

Here are some of the differences between owning the stocks and owning the metal:

Gold needs no corporate managers.
Stocks depend upon the best managers (with, by the way, the best of intentions).

Gold is an asset which is not simultaneously a liability.
Stocks often represent substantial debts and liabilities -- monetary, environmental, climactic, political, societal, etc.

Gold does not need a cash flow to survive. It is a stand-alone asset.
Stocks can't survive without it.

Gold does not require political and social stability to survive, in fact it thrives under the worst political conditions possible.
Stocks cannot survive without it.

Gold does not have to pay dividends.
Stocks depend upon solid profits in order to pay out dividends. How many actually do? If we go by Richard Russell's maxim not to buy a stock unless it does pay a dividend, that would narrow the field to about 25% of the listed stocks -- if that.

Gold is an asset of last resort.
Stocks cannot be used in a transactional situation.

Gold does not require an operating statement and balance sheet to determine value.
Stock values are determined by often complicated balance sheets and perceived future earnings.

Gold does not attract trend investors.
Stocks attract trend investors by the boatload. Keep in mind the gung-ho fund manager today could be the first to head for the exits tomorrow.

Gold does not rely on stocks for value.
Stocks rely on gold for value.

I could go on but I'll stop there. To be sure. . . .

Know thyself. And know what you want out of gold.

The metal itself for savings. The stocks for speculation.

Know why you are doing what you are doing when you do it. Many own both gold and stocks without knowing the purpose of each.

Finally, this is not posted to knock stocks, just to point out some of the potential folds and dark holes in that universe that should be present in any lawful prospectus. There are good reasons to own solid gold mining companies (or solid prospects) for the long run, but it has little to do with protecting one's assets except tangentially, that is, in so far as others move to protect their assets through safe haven gold ownership. But it does have everything to do with stock investing, and should be confined to the same rules applied to all other stock investing including employing all the analytical tools one can muster and finding a good advisor.