Hi again, I think i should tell the whole story of what im actually doing, hope it will shed some light. I posted an example in excel to the original post of what Im trying to do. The columns im after are date, time,currency pair, 5 min return and net ask-bid volume. But since the amount data is HUGE I need to sort it out is sas so i can use it in eviews for some modelling. First of all, the data needs to be separated into three files for eur/usd, use/chf and gbp/usd exchange rates. Then i need to calculate average of bid and ask prices per every second (if one of them is missing, then no averaging , for example, if no ask price, then ask=bid price). After that the 5 min ln return needs to be calculated (return=ln(everage bid-ask price per second 300/everage bid-ask price per second 1) starting from the beginning. For the bid and ask volume the size is enjoyingly expressed by letters from A to G. So the way i am thinking to find what i actually need (net ask-bid volume) is: count how many As, Bs, etc were there in the 5 min interval (the same interval for which return is calculated) separately for bid and ask volume. Then to assign specific value to each letter, multiply the amount of letter by the assigned value, sum them: for example: if there were 2 As and 3Bs for bid volume in 5 min interval (and assigned values are A=1, B=6), then 2*1+3*6=20 (5 min bid volume). the same goes for ask volume. the last step is 5 min ask volume minus 5 min bid volume = net order flow. I hope it sounds more clear now. Looking forward for any suggestions! Regards Anna
... View more