Hi,
I have a very large file of forex data that I wish to use to test trading strategies. I know that normally you'd have all the data you want in the spreadsheet and all the formulae replicated down. But this input file is way too big for that. Plus, I want to be able to design the one spreadsheet and then read different input files with it without having to create a different spreadsheet for each data file.
So what I want to do is read the first line of the file into, Let's say, A5 on the spreadsheet, then recalculate the spreadsheet, then read the next line of the file into A5 on the spreadsheet, and recalculate the spreadsheet again, right up until the end of the file. In other words, the relatively small spreadsheet will crawl it's way along the huge data file, continually recalculating as it goes.
I have done this previously, quite a few years ago, but annoyingly, that file was on a hard dish that crashed and I didn't have a back up. So I need to re-learn.
I'm aware that I COULD put all the calculations for each record into VBA, but that would be much harder than using the spreadsheet to do all the processing on each record.
Also absolute performance is not important. I don't care if the processing is inefficient and slow, what's more important is that it's easy to set up.
I've seen how to read a file within Excel, but the key bit is recalculating the spreadsheet based on the new line that's just been read, for each line. I haven't been able to find how to do that.
Any help gratefully received
Mike Hersee
I have a very large file of forex data that I wish to use to test trading strategies. I know that normally you'd have all the data you want in the spreadsheet and all the formulae replicated down. But this input file is way too big for that. Plus, I want to be able to design the one spreadsheet and then read different input files with it without having to create a different spreadsheet for each data file.
So what I want to do is read the first line of the file into, Let's say, A5 on the spreadsheet, then recalculate the spreadsheet, then read the next line of the file into A5 on the spreadsheet, and recalculate the spreadsheet again, right up until the end of the file. In other words, the relatively small spreadsheet will crawl it's way along the huge data file, continually recalculating as it goes.
I have done this previously, quite a few years ago, but annoyingly, that file was on a hard dish that crashed and I didn't have a back up. So I need to re-learn.
I'm aware that I COULD put all the calculations for each record into VBA, but that would be much harder than using the spreadsheet to do all the processing on each record.
Also absolute performance is not important. I don't care if the processing is inefficient and slow, what's more important is that it's easy to set up.
I've seen how to read a file within Excel, but the key bit is recalculating the spreadsheet based on the new line that's just been read, for each line. I haven't been able to find how to do that.
Any help gratefully received
Mike Hersee
