Homepage
PR Newswire
18-06-2014, 07:23 PM
I wrote a simple application to calculate volume-weighted average stock price for multiple quarters and populate an Excel file.
I use it to calculate historical PE, dividend yield and so on.
I would like to share it in case anyone else finds this useful.
*Requires Excel 2010 or 2013*
Your excel file must follow this format:
- Each column stores information about one period (Quarter, half-year, etc).
- Columns must be ordered from most recent (left) to oldest (right).
- You need a row named "Period End Date" that contains the end date of the period in dd/MM/yyyy format. Row number does not matter.
- You need an row named "Historical Price". The tool will update this row. Row number does not matter.
See \TestTool\Sample\C38U.xlsx for an example. (Required rows highlighted in blue)
*Excel files downloaded from DBS Vickers Clarity already satisfy the first three conditions, so you only need to add an empty "Historical Price" row somewhere.
To use:
1) Download the ZIP file and extract somewhere on your PC.
2) Double-click <unzip folder>\TestTool\bin\Debug\TestTool.exe.
3) In the [Symbol] box, type in the stock symbol without ".SI". Currently only works on SGX-listed stocks.
4) In the [Fin report offset], type in the approx. number of days the company usually releases the financial reports after the end of the period.
The purpose is to adjust the period to get price average after quarterly reports have been priced in.
You can leave it as 0 if you can't be bothered.
5) In the [Excel filepath] box, paste the path of your excel file to update. For demo, use \TestTool\Sample\C38U.xlsx.
6) Click [Go] to update the excel file. A message box appears when update is complete.
7) Click [Open].
Some notes:
1) The program helps you to backup the excel file before modifying it. (You can also backup yourself for double safety)
2) Tool tries to get stock price data from both yahoo and google. The website with more data is used.
3) Price calculated is the volume-weighted average for the period. Formula is:
Sum of ((Low+High)/2 x Volume) for all days in the period/Total volume in the period.
The period is offset by the value you type in [Fin report offset].
For example, if [Fin report offset] is set to 20 and the period is 2014/1/1 to 2014/3/31, resultant period will be 2014/1/21 - 2013/4/20.
4) Tool currently only updates first sheet in the Excel.
The software is provided "as is", without warranty of any kind.
Let me know if you have suggestions or find bugs.
Source code is included so feel free to customize if you are familar with C#.
I use it to calculate historical PE, dividend yield and so on.
I would like to share it in case anyone else finds this useful.
*Requires Excel 2010 or 2013*
Your excel file must follow this format:
- Each column stores information about one period (Quarter, half-year, etc).
- Columns must be ordered from most recent (left) to oldest (right).
- You need a row named "Period End Date" that contains the end date of the period in dd/MM/yyyy format. Row number does not matter.
- You need an row named "Historical Price". The tool will update this row. Row number does not matter.
See \TestTool\Sample\C38U.xlsx for an example. (Required rows highlighted in blue)
*Excel files downloaded from DBS Vickers Clarity already satisfy the first three conditions, so you only need to add an empty "Historical Price" row somewhere.
To use:
1) Download the ZIP file and extract somewhere on your PC.
2) Double-click <unzip folder>\TestTool\bin\Debug\TestTool.exe.
3) In the [Symbol] box, type in the stock symbol without ".SI". Currently only works on SGX-listed stocks.
4) In the [Fin report offset], type in the approx. number of days the company usually releases the financial reports after the end of the period.
The purpose is to adjust the period to get price average after quarterly reports have been priced in.
You can leave it as 0 if you can't be bothered.
5) In the [Excel filepath] box, paste the path of your excel file to update. For demo, use \TestTool\Sample\C38U.xlsx.
6) Click [Go] to update the excel file. A message box appears when update is complete.
7) Click [Open].
Some notes:
1) The program helps you to backup the excel file before modifying it. (You can also backup yourself for double safety)
2) Tool tries to get stock price data from both yahoo and google. The website with more data is used.
3) Price calculated is the volume-weighted average for the period. Formula is:
Sum of ((Low+High)/2 x Volume) for all days in the period/Total volume in the period.
The period is offset by the value you type in [Fin report offset].
For example, if [Fin report offset] is set to 20 and the period is 2014/1/1 to 2014/3/31, resultant period will be 2014/1/21 - 2013/4/20.
4) Tool currently only updates first sheet in the Excel.
The software is provided "as is", without warranty of any kind.
Let me know if you have suggestions or find bugs.
Source code is included so feel free to customize if you are familar with C#.