A major objective of the Jaanga web site is to provide you with easy-to-use tools for viewing large quantities of numbers. Certainly some of the techniques presented here may be complex or time-consuming, but there should always also be some quite accessible methods.As they say:
Space travel won't take off until everyone does rocket science in backyards.
The accompanying chart can be use to monitor up to fifty stock symbols and indices. The sample file displays all thirty stocks in the Dow Jones Industrial Average - all editable, zoomable and clickable. There may be some rocket science here, but it's just serving to make the technology more accessible.
The chart is accessible in three ways: First, you can grasp its meaning quite quickly.
Even with this simple chart you are visualizing 150 factors: thirty stocks multiplied by:
- price volatility: X-axis
- volume volatility: Y-axis
- relative market capitalization: radius
- industry sector: color
- symbol name
The second accessible aspect is that editing and updating the chart can be carried out by anybody with entry-level spreadsheet skills.
The third accessible aspect is is so obvious that is is too often forgotten: that the software and data are available at no charge or install and setup time.
1. Editing the Data
Edit the stock symbols in Column A. Feel free to add or delete rows. An easy start is to add "AAPL" (the stock symbol for Apple Computer) to row32. Rows 33 to 35 show you how indexes should be entered.
2. Updating the Calculations
First select the cells in Row 2 from column B to H.
Then use Auto-Fill feature by dragging the little box at the bottom-right of the selection to select all the rows below. An info-graphic for Auto-Fill is given here. Alternatively, if you are a regular spreadsheet user, you can use the Control/Command-D Fill-Down command.
Within a minute or so all the cells should update.
3. Updating the Chart
Note the number of rows you now have in Sheet1.
Click on the Chart1 tab to bring up the Chart1 worksheet.
In the top right click on Edit
Update the row number as shown in the Select Range text box by changing the number after the "H".
Click Update.
Suggestions
Move the chart worksheet over to the left (click on the arrow in the tab). This will cause the chart to load and appear first every time you open the spreadsheet.Check the symbol name of a stock using Google Finance.
The stock market data is updated every minute or so.
The prices shown are near-real-time while the volumes are 15 minute delayed. The notice on the chart says that prices may be delayed but, in our experience, such delays are highly infrequent.
The chart is built using Google Docs. You will need to register with Google in order to edit the spreadsheet.
Tech Notes
The size of the spreadsheet is limited by the number of Google LookUp items in column F. The total currently allowed limit is 50 look-up items per document. If you copy the sector names then paste them back in using Edit menu > Paste Values Only, you will not only significantly speed up the spreadsheet but can also have up to 250 rows of data.The total number of Google Finance items is 1,000. This spreadsheet uses GoogleFinance in four columns, therefore there is a maximum of 250 rows of data.
The functions used in the spreadsheet are straightforward. They just make use of the great power built into Google Spreadsheets. Here are five of the seven functions used:
Row 2 - Column B
=A2 & " - " & GoogleFinance(A2,"name")
Row 2 - Column C
=value(hour(now())& right("0" & minute(now()),2))
Row 2 - Column D
=GoogleFinance(A2,"changepct")
Row 2 - Column E
=100*GoogleFinance(A2,"volume")/H2
Row 2 - Column F
=index(importHTML("http://finance.yahoo.com/q/pr?s=" & +A2 & "+Profile";"table";10),2,2)
Later posts will cover techniques for handling up to thousands of symbols per chart - but will be more complicated to put together than this chart. We will also create motion charts that will allow you to replay the daily movement of the stock markets.
Link: Jaanga gDocs EasyStart
No comments:
Post a Comment