I have an existing excel file that records historical stock transactions and related statistical data from stock market with DDE function supported. I’d like to enhance it by automation to minimize manual input. VBA or even simpler methods are appreciated, as long as the requirement can be fulfilled.
Basically, my proposed upgrade will include the following:
1) Bulk data update
My excel should be able to feed in daily transactions (no of entries varies everyday) and calculate the net gain/loss for each pair of settled transactions. Related data such as transaction ID, admin fees, etc can be parameter driven to avoid hard-coding (the net gain/loss formula is already present). Please note that the excel should be able to handle overnight transactions (i.e. buy/sell of the transaction are on different bulk files.) The records are separated by month and by sheets in the excel. Monthly sheet can be generated automatically so that the sheet copy step can even be saved.
2) Scrap free real time information from some financial websites (mainly text. Appreciated if non-text figures can be drawn)
3) Decode common csv files from the exchange company (all data formats would be given) and select some of the data to fill in my excel (very straightforward, like taking the closed price of a certain investment product)
4) Automatically update net open positions of investment products traded in (1) and quote daily close price to estimate unrealized profit/loss.
5) Simple alert function – change the color of the real time data like stock price or even offer sound effect when preset price is reached.
6) Using the DDE real time data feed, construct the following common technical indicators: RSI, MACD, STC, SAR, BB, BB bandwidth, %B. The time period of the technical indicators can be parameter driven, like 1-min/5-min/15-min/hrly/daily/weekly/monthly.
7) Automatic plotting of simple x/y-axis line chart of a set of data
8) More advanced functions using DDE: option screen with delta, gamma, vega, theta and implied volatility (IV).
9) Construction of Market Profile using DDE functions
For (8) and (9), it’s more advanced. Total or partial completion of (1) to (7) is also acceptable.
More detailed comments are provided in the attached excel.
To reiterate, the program needn’t be fancy but accuracy is what I demand most.