MoneyPlanters
New Member
- Joined
- Apr 9, 2011
- Messages
- 41
Please have a look at the attached files. the primary objective is to automate as much as possible in order to reduce human intervention. I shall now explain every bit of what I need to do, step my step.
1. Everyday, a 3rd party application downloads financial data in the form of a CSV file (refer to data.csv attachment).
2. I save this file as data.xlsx (as attached)
3. Now I fetch data from data.xlsx into sample.xlsx.
At this point I need to clarify something. If you notice, you will find that A1 on sample.xlsx is filled with a formula =Data.xls!A1. However, when I make changes to the data.xlsx file and save it, values on sample.xlsx are not updated. Please help me with the settings that need to be implemented in order to achieve this.
4. We now switch to the 'Daily' sheet on sample.xlsx. I need Excel to fetch the latest Open, High, Low and Close values for each ticker based on the dates available on the 'Data' sheet (i.e. the most recent day's data). Please note that the 'Data' sheet will always contain one year's data for each ticker.
5. We now switch to the 'Weekly' sheet on sample.xlsx. I need Excel to fetch the latest weekly Open, High, Low and Close values for each ticker based on the dates available on the 'Data' sheet (i.e. the most recent week's data). Please note that the 'Data' sheet will always contain one year's data for each ticker. At this point I should mention a few points:
Weekly OPEN means the OPEN value for the first day of the week.
Weekly CLOSE means the CLOSE value for the last day of the week.
Weekly HIGH means the Highest of all the HIGH values for the week.
Weekly LOW means the Lowest of all the LOW values for the week.
If there are holidays in a week, then weekly data should be calculated as per the days available within that particular calendar week. Usually there are 5 working days in a week. Dates from the next week should not be used to make up for holidays.
I shall be extremely grateful to you if you could help me on these and send me an updated file
1. Everyday, a 3rd party application downloads financial data in the form of a CSV file (refer to data.csv attachment).
2. I save this file as data.xlsx (as attached)
3. Now I fetch data from data.xlsx into sample.xlsx.
At this point I need to clarify something. If you notice, you will find that A1 on sample.xlsx is filled with a formula =Data.xls!A1. However, when I make changes to the data.xlsx file and save it, values on sample.xlsx are not updated. Please help me with the settings that need to be implemented in order to achieve this.
4. We now switch to the 'Daily' sheet on sample.xlsx. I need Excel to fetch the latest Open, High, Low and Close values for each ticker based on the dates available on the 'Data' sheet (i.e. the most recent day's data). Please note that the 'Data' sheet will always contain one year's data for each ticker.
5. We now switch to the 'Weekly' sheet on sample.xlsx. I need Excel to fetch the latest weekly Open, High, Low and Close values for each ticker based on the dates available on the 'Data' sheet (i.e. the most recent week's data). Please note that the 'Data' sheet will always contain one year's data for each ticker. At this point I should mention a few points:
Weekly OPEN means the OPEN value for the first day of the week.
Weekly CLOSE means the CLOSE value for the last day of the week.
Weekly HIGH means the Highest of all the HIGH values for the week.
Weekly LOW means the Lowest of all the LOW values for the week.
If there are holidays in a week, then weekly data should be calculated as per the days available within that particular calendar week. Usually there are 5 working days in a week. Dates from the next week should not be used to make up for holidays.
I shall be extremely grateful to you if you could help me on these and send me an updated file
