Thanks for reading this project. What I'm looking for is for someone to do the coding behind the attached excel spreadsheet. Explanatory notes are as follows:
- All sheets- must automatically update on spreadsheet opening
- The stock names in column A in all sheets (aside from overview and commodities) must automatically be in alphabetical order. It must also be flexible enough to allow me to delete a stock name in column A if no longer needed. Similarly, I should be able to add further stock names (up to and including A28, ie each sheet can include a maximum of 27 stocks)
- Stock share price, shares on issue and volume of shares traded can be sourced from anywhere you like. The source should be free and a long term source. I have the amibroker program installed so information regarding stock share price+volume of shares traded may be obtained through that. Other sources of information could include [url removed, login to view]
- The excel document should open quickly and not lag
% Now - the % change in price since open if currently trading, otherwise the % change in price for the last day of trading
% 30 day - the % change in price from todays current price to the closing price 30 days ago
Value - the current price of the stock/benchmark
The top 2 rows should stay in their position with one chart underneath it per page (ie if you press page down you get to the next chart)
XEC, S&P 500, XSO, ASX 200 have both 1 year and 5 year charts - the 5 year one should come first then the 1 year one. Chart the different symbols in the same order they are listed on the spreadsheet. The symbol for the chart (ie XEC) should be above the chart, hyperlinked to the source website of the chart.
S&P 500 futures % info should come from the following page: [url removed, login to view]:30&density=X&pricesOn=1&asPctChange=0&logscale=1&im=30&sym=ESH18&grid=1&height=500&studyheight=100
Same explanation as the "overview" sheet
SP - Current share price
SOI - Shares on issue
MC (millions of dollars) - SP*SOI
Volume (ST) and Volume (MT) are cells that turn green if their formula is true; the formula for volume (ST) contains two variables, u and v, and the formula for volume (MT) contains two variables, w and x.
'u' and 'v', and 'w' and 'x', all equal cumulative trading volumes for different numbers of days. ie if u=3 then this returns the cumulative volume for the last 3 days of trading (including today if there is data available for today). 'v' is a user customisable number of days related to 'u', ie if v=6 then this cell returns the cumulative volume for the last 6 days of trading prior to the period 'u'. 'w' and 'x' are similar to 'u' and 'v' but just a different number of days (ie w=5, x=30)
An example of a formula for the volume (ST) column would be 'u'>2*'v'. If the formula is true then the cell turns green. The formula should be easily user customisable as should the day value of 'u', 'v', 'w' and 'x'.
SP7 - = ((SP today)/(SP 7 days ago)-1)*100 (7 is the default, but it should be a user customisable number of days). Same idea with SP30 column but just a different number of days
Flag (column H) - This cell turns green if the value for column G exceeds a user customisable number (default 15%)
Ann. - This cell should have a hyperlink in it that links to the ASX page for the company listed on that row that has their announcements for the last 6 months. An example would be for the company PLS ([url removed, login to view])
Notes - Just a blank cell for me to input notes
These are blank templates that I can fill out if wanted. They must be coded in such a way that I can copy and paste these templates so I can have even more spreadsheets if needed
Please only bid for the project if you can complete all aspects of the project. Thanks