An I.T. System to Track Share Prices
Objective
My dad has asked me to create an Excel Spreadsheet, which will track his share prices, as currently the quotes are only being recorded on paper, making it extremely difficult to plot graphs etc and manage it efficiently.
However hopefully I will overcome this problem and allow easy automatic calculations through MS Excel.
Also my user is not certain when to sell and buy shares so I will make an automated process in which my spreadsheet will decide when to take action by informing the user.
Hyperlinks will be used to take the user to news on shares etc, to help make decisions on whether to buy and sell. A yearly graph on the company's shares will be present with the visual basic program, which will be updated automatically.
My user is new to MS Excel, so I will need to make it as automatic as possible.
Formulae
What it will help me do briefly
Sum
Add up specific numbers.
Average
Calculate average.
Hyperlink
Link to sites, workbooks etc.
Rand
Create data to work from.
Product
Multiply cells.
Vlookup
Compare values in a lookup table.
Round
Round a number to specified decimal place.
If
If... Then... Else
Graphs
Plot share prices etc
What Software am I Going To Use?
I am going to use Microsoft Excel for a number of reasons, as this package has many functions.
As my user is new to Excel I will need to make use of Command buttons.
Below I have illustrated some other important functions in MS Excel.
Cells can easily be identified.
Formulas can easily be entered.
As shown above, calculations can be easily done in Excel, making it user friendly.
Finally to make my spreadsheet more attractive I can use the below simple tools.
MS Word Art Drawing Tools & Shapes ClipArt's Shadow and 3d style
Free rotate Basic drawing shapes Fill Colour
What other packages can also do the job?
Software Name
Advantages
Disadvantages
MS Excel
Simple user interface with many facilities.
If data in one cell is changed accidentally all results may be altered.
MS Access
This software is a relational database and is not a spreadsheet so cannot be compared.
Lotus 123
Simple user interface with many facilities.
In some ways better than Excel.
Same as excel.
Claris Works Spreadsheet
Simple to use with common basic functions.
Not a familiar interface and lacks the power of macros etc.
MS Works Spreadsheet
Similar to MS Office software as MS Works suite provides a Word Processor and a database.
Not enough advanced features.
Star Office Spreadsheet
Software suite is free.
Also has a word processor.
Extremely basic, lacks to many key features, which Excel contains.
From the above table I can see that MS Excel and Lotus 123 are best suited to my task, as they have a simple layout with great advanced features, including macros, which other packages fail to provide. Both packages seem extremely versatile and powerful. MS Excel has a great help facility also. VBA code and forms can be created in Microsoft Excel, making it very unique.
I will use MS Excel to create the spreadsheet, as I am most familiar with it and also currently the user has a tight budget and is not willing to invest any more money. With excel I will also be able to edit any macro visual basic code, as this will be required later on in the project.
What am I personally hoping to achieve?
I am aiming to expand my Excel knowledge and VBA knowledge and create a 90 % automated system, in which my user just has to click a button.
Analysis
My approach to the task:
As currently the share prices and quotes are only being extracted from one site, which is www.netscape.com, I have decided that my user will be requiring other resources and sites for the latest news and decisions.
Also the user is uncertain which data and text applies to which company so ...
This is a preview of the whole essay
What am I personally hoping to achieve?
I am aiming to expand my Excel knowledge and VBA knowledge and create a 90 % automated system, in which my user just has to click a button.
Analysis
My approach to the task:
As currently the share prices and quotes are only being extracted from one site, which is www.netscape.com, I have decided that my user will be requiring other resources and sites for the latest news and decisions.
Also the user is uncertain which data and text applies to which company so by adding comments to cells I will hopefully resolve the issue.
My users data is also extremely unorganised, as weekly he receives his share prices and quotes through the mail and how much profit etc he is making. My worksheet however will hopefully make the mail prices obsolete, as the calculations will be done through Excel more regularly.
So in this project I will be creating the following -
Worksheets - Different aspects of the shares will be put on multiple worksheets, which will all be linked to one another.
Quotes - Will be taken from visual basic program.
News - Will be taken from real player and CNBC channel through a TV Card and also other various sites.
Graphs - Quotes will be plotted automatically as data is entered.
Hyperlinks - Take my user to my VB program and relevant sites or cells.
This information will consists of the following Data Structure's -
Type Of Data Structure
Reason for type of information
Text
Company names, news and comments will be made up of text.
Numbers
Company share quotes, will require the use of numbers, which will be presented in graphs. Also all calculations will be done with numbers using formulae.
Pages will also be numbered.
Graphics
Little graphics will be used, to display the company logo, which can be clicked as a hyperlink to take the user to their homepage.
Scanned Images
A few backups of existing letters, which are posted, will be made.
Alignment
The data will be separated through alignment and formatting.
Date
Dates will be included to help organize data.
Templates
This will be a very handy shortcut when it comes to writing letters and studying a particular company.
Downloads
This is a type of data structure, when I download pictures, or files form the Internet.
Data Collection
DATA
WHERE IT WILL COME FROM
WHAT I WILL DO WITH IT
WHAT IT WILL HELP TO DO
Quotes -
My visual basic program and general internet websites.
This information will be plotted on a graph daily.
It will help work out loses and gains and future companies.
Facts -
Will generally come from websites, real player and CNBC news.
I will display the facts in an understandable fashion in comments.
It will help my user to make decisions.
News -
Will generally come from websites, real player and CNBC news.
The user may wish to take action after viewing the latest news.
It will allow the user to make decisions.
Page number -
Previous page number plus one!
It will help organize and track the share values.
It will allow the user to browse more efficiently.
Company logos -
From their website.
I will use it as a hyperlink.
Quickly move to the company's homepage.
Graphs -
From my VB program and the internet.
I will use it to display my quotes, profits etc.
Help me make decisions.
The Internet
This will probably play the biggest role in my research as it holds an unbelievable amount of information, as now a website can be created not only by a programming language such as HTML, but by simply dragging and dropping items of a selected choice, using such services as Homestead or by creating it manually using Dream weaver or Microsoft FrontPage and uploading it on a free server.
Also I will use real player channel WSJ Market Update to get the latest information on the market shares that is done through the Internet.
Through the Internet I will be able to send and receive text and images via email and web browsing. Also I will have access to many quotes, news, and general gossip, as I will be able to view current issues from a wide audience of resources.
However a down side to the Internet will be costs and connection charges. But recently Ntl have launched UK's first free Internet service.
Also the Internet can act as storage and backup documents using Myspace and such other services. However this can be very time consuming and so will not be used for now. But hopefully one day my user will have access to a fast internet connection such as Ntl's new 512k/s cable modem service or ISDN and then will be able to access there files from any computer connected to the internet in the world.
* The user - I have checked too make sure that the final user will have access to the Internet and all is well and good.
Data Types
Data Type
When will I use it?
What will it accomplish?
Formulae
I will use it when I believe the data inside the cell will be updated regularly.
It will perform complex calculations quickly and update new cells as required.
Graphs
When I want to plot my share quotes, gains, loses etc.
Give a better overview of the data and help make easier comparisons.
Mail Merge
When I want to send out a letter to more than one person.
Not likely to be used.
Macro's
When I want to perform a function easily by pressing an assigned key.
It will save time, as it will shorten operations.
Formatting
When I want to place an object/text somewhere specific.
It will give a professional look and layout.
Charts
When I want to turn a table into a graph.
Give a better overview of the data and help make easier comparisons.
Formulae
What it will help me do briefly
Sum
Add up specific numbers.
Average
Calculate average.
Hyperlink
Link to sites, workbooks etc.
Rand
Create data to work from.
Product
Multiply cells.
Vlookup
Compare values in a lookup table.
Round
Round a number to specified decimal place.
If
If... Then... Else
All the above data types will be used throughout the worksheets, as hopefully they will help to improve the visual layout and organise the information, data. The formulas with perform all calculations automatically. Ideally as many macros as possible will be used to help improve speed, as this will play a crucial role in the production of the excel worksheets.
Combined with the data structures and data collection my spreadsheet will be created.
Is it worth producing the spreadsheet?
As mentioned in Identify, my dad currently does not have an efficient method to track his share prices, as all the data is recorded by hand on paper. This means he is uncertain how he is doing, as many calculations cannot be done regularly. Decisions on when to buy and sell are also extremely tough as my dad lacks the resources and the latest news.
The new spreadsheet will hopefully resolve all these problems and make tracking and organizing the shares a lot better.
Hardware Requirements
Currently my user's PC matches all required specifications.
With this machine I will have no problems running most software. I will be using MS Office 97 on MS Win 98 operating system. The printer will be capable of doing 10 pages per minute and the modem will be used to access the Internet and real player.
* Currently my user has a satellite Arial going into the room containing the PC, so I will add a PCI TV Card to the machine so that my user may connect up the satellite and view CNBC Market Channel, to have access to the latest news and share quotes.
Evaluation
Introduction
My system has been created, tested and the faults have been highlighted. My system was not flawless and due to a lack of time I could not remove any mistakes. However, I successfully managed to do the following -
* I created a spreadsheet, which automatically was able to extract information from the Internet and place it within the worksheet.
* I managed to make all calculations automatic and profits were worked out with no hassle.
* I managed to take into consideration the prices at which my user bought his shares.
* I managed to keep the layout effective and simple, by hiding worksheet tabs, making everything command button based.
* I successfully created an auto open macro and an auto close macro, which acted as planned.
* I plotted the companies share values as planned, on a single spreadsheet.
* I set up an advice system and the computer suggested what action my user should take.
* I managed to put in all share prices and the date in the required spot, with a click of a command button.
* I set up hyperlinks to specific web pages for my user, as he only has to click on the related image.
* I made it very user friendly by shortening it down to only 2 worksheets.
* I set up a greeting and goodbye message box for my user.
As good as the facilities I listed above were, I failed to take into consideration that the system could not be used at all unless my user was connected to the Internet. Using the pen paper system however, my user could calculate profits without the use of the Internet. This was a huge flaw in my design. As a result when the system was opened up without being connected to the Internet, many run time errors occurred, making it very user-unfriendly!
What my users final thoughts were?
My user was not to put off by the fact the spreadsheet could not be opened up without the Internet, as he said he had access to the Internet all the time.
He said the front-end was very user-friendly as it appeared to him like a normal application with simple instructions.
He liked the idea of only having 2 worksheets as he said it simplified his task.
He liked where the graphs were placed and said now that it was on computer he could easily track and update values all the time and predict future prospects from the graphs.
My user liked the way he could easily enter the values at which he bought the shares at by simply following the instructions and entering single values, as previously all calculations would have to be redone.
To round up my spreadsheet the user commented, "It is very user-friendly and simple for the tasks which would previously have taken me minutes not seconds! However it lacks the ability to work whilst being connected to the Internet, so this is a major drawback."
Conclusion
Overall I feel that my solution is not a 100 % effective and that my user is a lot more satisfied with it than I am. To finish I will compare what I achieved, with what I said I was going to do in my identify section, shown below.
My dad has asked me to create an Excel Spreadsheet, which will track his share prices, as currently the quotes are only being recorded on paper, making it extremely difficult to plot graphs etc and manage it efficiently.
However hopefully I will overcome this problem and allow easy automatic calculations through MS Excel.
Also my user is not certain when to sell and buy shares so I will make an automated process in which my spreadsheet will decide when to take action by informing the user.
Hyperlinks will be used to take the user to news on shares etc, to help make decisions on whether to buy and sell. A yearly graph on the company's shares will be present with the visual basic program, which will be updated automatically.
My user is new to MS Excel, so I will need to make it as automatic as possible.
Track his share prices - I successfully got share prices from the Internet to allow tracking.
Plot graphs - I created a graph for each individual company.
Automatic calculations - All calculations were automatic.
Sell and buy shares so I will make an automated process - I made an automatic system and at a click of a button my user was advised.
Hyperlinks will be used to take the user to news on shares - I created Hyperlinks to each sites default homepage.
I completed all that I intended and what my user wanted me to do successfully, but failed to satisfy myself.
Navad Khanzada
