Excel question

O

omniscient

Guest
i have several years of data for several instruments (in CSV format) and would like to be able to pull specific data from the files without having to manually filter, sort, and formulate it all.

for example, i would like to be able to pull the overnight extremes for a given range of dates. so, on Day 1, i would get the H/L (Max and Min) from 16:30 Day to 09:30 Day 2. on Day 2, i would get the H/L between 16:30 Day 2 and Day 3. and so on and so forth.

there are other things i would like to be able to pull from the data, but if someone has a suggestion/solution for this particular scenario, i think i can adapt it to other sets of data i would like to extract.

if additional information or details are needed, please feel free to ask.

thanks, in advance, for the assistance.

take care -

omni
 
I do this all the time. I have Office XP 2003

Go to:
Data-Import External Data-Import Data

Select the CSV file. Excel will give you various formatting options and enter data that is separated by a command in to cells.

It's pretty easy. Unless I'm not understanding your inquiry.
 
Quote from Spectra:

I do this all the time. I have Office XP 2003

Go to:
Data-Import External Data-Import Data

Select the CSV file. Excel will give you various formatting options and enter data that is separated by a command in to cells.

It's pretty easy. Unless I'm not understanding your inquiry.
More...

Spectra, thx for your reply, but that isn't quite what i am trying to achieve.

i have a CSV file that has data for a given time-frame for a given set of dates. for example, i have included an image that shows 30min data for Jan 8 & 9, 2007. it is 24hr data and i want to extract specific data (in this case, the high and low between RTH closing and RTH opening; this section of data is highlighted in blue). i have another image that shows the same data filtered for the times i am looking for and columns added with formulas created producing the H/L and Range for the specific spans of time.

here is the first image:
 

Attachments

so i am trying to find an efficient way to extract specific information based on specific conditions over certain timeframes. it's very easy to find OHLC data for RTH, but i want to do the same for other times. in this example instead of getting OHLC data from Open to Close, i want to find it for Close to Open.

as previously mentioned, i want to do this for other times, timeframes, and conditions as well, but thought this would be the easiest example to use.

again, if more information is needed, let me know and i'll explain further.

also, i don't necessarily have to do this WITHIN Excel. if i need to look into scripting some function to scan my CSV files, grab the desired data, and spit it out into a raw TXT file or something like that, i'm game for that as well.

and, again, thanks in advance for the help.

and here is the second image with the data filtered and calculations performed to get the H/L and Range for a particular Overnight session.
 

Attachments

Quote from Bowgett:

It should be pretty easy to code in VB or C#.
More...

that's kind of what i was wondering. and i'm reaching the point where i think it will be more useful and efficient to learn some coding/scripting to accomplish it versus clunking along manually. i'm no coder, but i have things i want done, so i guess it is time to learn ...

either VB or C# any easier for a newbie coder to grasp? or is it like most other things: it depends. :D

thx
 
If you go into automated trading you will have to learn something more than Excel :) VB, C#, C++, Java, Perl etc etc
 
Quote from Bowgett:

If you go into automated trading you will have to learn something more than Excel :) VB, C#, C++, Java, Perl etc etc
More...

while i am a long way from ATS, i am very encouraged by tonight's progress. i'm using VBScript (i've used it a bit to create sessions and stuff in SecureCRT). i think i will be able to create a script that will do exactly what i am trying to accomplish. more later.

thx again ...
 
Go to Mr Excel, it's a site geared for Excel and they can and will answer any question you have. It is hands down one of the most helpful sites I've ever seen.
 
go to data
Pivot table
lead it to to your source file
This uses SQL to do the JOB
a bit complex at first but will get the hang of soon
GL
 
You must log in or register to reply here.
Back
Top