automate daily tasks

    Hey guys,


    there is something I need to do daily and it takes me about 30min so I thought maybe it's possible to automate the steps I go through.


    I already created the macro that will do the first step and need help with the second step. If you look at the uploaded sheet, you can just run the first macro and it will do some sorting and stuff which I was able to do. The next step is a little bit more complicated. Once step one has been done and the columns have been shifted around and the data has been sorted I want to do this:


    The first column will show you the Instrument Codes and the second column will show you the Open Positions. I want the macro to go through the instrument codes, and every time it sees the same instrument code listed multiple times, I want it to take the coresponding open positions and add them up and have the sum listed under one security code and delete the remaining. For example:


    Before:


    Code
    Instrument Code                  Open Positions
4004.T                               1000
4004.T                               2000
4004.T                               500


    After:


    Code
    Instrument Code                   Open Positions
4004.T                                3500


    That's about it. It woudl be useful to have this macro be called step2, so I can look at it in the future, in case I need to make changes.


    Thank you very much.

    Re: automate daily tasks


    Try the following:



    Rather than deleting the oter lines, it subtotals the lines and then hides the details so just the subtotals are showing.


    Also, when I ran your step 1 macro, it errored out on the following:


    Code
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal



    I Changed it to:

    Code
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    and it seemed to work



    line

    Re: automate daily tasks


    The data is there, just not in the subtotal row. It is in the detail rows. I'll see what I can do for you. I'm not sure if there is a way for the subtotal row to show the other details or not.

    Re: automate daily tasks


    Quote from sevan17

    also, before step1 and 2 happen, I need all rows that show AX in column exchange to be deleted.


    You can add:


    Code
    Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="AX"
    Rows("112:522").Select
    Selection.Delete Shift:=xlUp


    to the beginning of the macro and it will filter the rows with "AX" in the exchange column and delet them.


    Still looking at the other part of the problem.

    Re: automate daily tasks


    the code for the "AX" filtering doesn't work on my sheet. Are you sure it is correct?


    Well it works, but once it is filtered I have to click on show all before it shows it all to me.

    Re: automate daily tasks


    also, can we also filter out SFE in addition to AX (same column)? Thanks


    :) Sorry for bothering you again, but I just realized that there are also some blank rows inbetween the data, is it possible to filter those out as well?
    I knwo how to do basic filtering, but I am not too sure about how to customize it that's why I am asking so many questions :)


    Thank you very much for your time.

    Re: automate daily tasks


    sevan17,


    Ok this is the code that I came up with. It updates the Open Pos column.



    Re: automate daily tasks


    I just tried it out. Take a look at my new attachement. First play the first macro (step1), and the the second (which is yours). Before you play the second macro you can scroll down towards the end and you will see the Instrument code SSIU5. When you manually add up the positions for that particular instrument code, you will get a sum of -877. When you apply your macro (step2), the sum ends up being -76, What it does is, it just leaves one of the instrument codes and its coresponding position and deletes the rest without summing them up.


    Also I had a macro at the beginning which was supposed to filter out anything that has an "AUD" as its instrument Currency. This can happen before or after step1, but the coresponding column will change once you apply step1.


    THank you.


    P.S.: Sometimes the sum will equal to 0, which means excel should just delete all the fields. Tricky huh?

    Re: automate daily tasks


    nicee, that works. Ya I can tell, my computer needs like 10sec to do the macros :)


    thanks, that's great.


    Would you guys mind if I add some more steps to it?

    Re: automate daily tasks


    I am actually at work and will go home very soon, in case you guys don't mind taking a look at my idea I would really appreciate it:


    I will have a second sheet that should be compared to the sheet we created when we ran the macro. I want to match the Open Pos from each to each other.


    1. When the Open Positions are not equal to each other, I need the rows to be highlighted in yellow


    2. When they are equal to each other nothing should happen.


    3. When there is an instrument that is on the sheet that you have been working on so far but not on the new sheet, the row should be highlighted in blue.


    I am not even sure if something like this is possible. I am sure I am asking for a lot, but there is no way I can do this, given the fact that I know 0 about VBA, but got to do this exercise every day.


    P.S.: I have uploaded a sample of the second sheet.


    Thank you very much guys, and have a good night.

    Re: automate daily tasks


    sevan17,


    I re-read the post and this loop takes care of the initial parameters that you requested ( atleast I think so )






    As far as:


    Quote from sevan17

    1. When the Open Positions are not equal to each other, I need the rows to be highlighted in yellow


    2. When they are equal to each other nothing should happen.


    3. When there is an instrument that is on the sheet that you have been working on so far but not on the new sheet, the row should be highlighted in blue.


    Your adding more and more items without telling us how the previous items tie in. Which ( IMHO ) is making it difficult to be able to create the code efficiently.



    For instance how does the original openpos.xls file relate to the new file you posted. Are they actually 2 different workbooks. Should the data summarized in openpos.xls be posted in the new workbook, or is their a third workbook which opens both the workbooks and then performs the actions and saves?????



    I'm hoping that


    Quote

    I am not even sure if something like this is possible. I am sure I am asking for a lot, but there is no way I can do this, given the fact that I know 0 about VBA, but got to do this exercise every day.


    with the information that you're gaining experience. These items you're requesting are not difficult and once learned you can improve your productivity.

    Re: automate daily tasks


    Hi,


    first of all thank you very much for the great work.


    I thought splitting this whole project into little pieces will make it easier to understand, since the whole process is a little bit confusing. But I also understand your point.


    Let me give you a little bit background on this project.
    We have a front office and a back office in our firm. The front office does the trades and records them, and the back office does the its own recording independently. At the end of each day the front and back office compare each others trades. When I say compare, we are comparing the column called Open Positions. The should all match up, however, they never do. There are always trades that the back office has which the front office doesn't, and sometimes the quantity (Open Position) doesn't match. So I have to go through them every day and find the ones that don't match to point them out to the trader and the back office, and the way I do it is by maually comparing the numbers and highlighting missing or mismatched trades.


    The excel sheet you have done the macro on is the main sheet, which comes from the back office. Let's call it the main list, it is the most accurate one. The second sheet I uploaded comes from the trader. The trader and the back office email me two seperate worksheets every day. I do not combine them (even though I could, nobody would really care), but I compare them to each other. The results, meaning missing trades or mismatched positions are recorded on the main sheet (the one you have doen the macro on), and then being send back to the back office and the trader. The traders sheet gets deleted once it has been used for comparing.


    The way the whole thing works is that I scan through them, and when I find Instrument codes on teh main list that are not listed on the traders list, I highlight the row which has the missing trade on the main sheet with light blue. When I find security code that has an Open Position that is different than the one on the traders worksheet, I highlight it with yellow.


    I hope this gives you a better understanding of the relationship among those two sheets.


    I believe the first thing that has to be done on the traders worksheet (second worksheet uploaded) is to sort the positions in the same way we sort the other sheet, from there the macro has to go through the trades one by one and find mismatched open positiosn or missing instrument codes.


    I am trying very hard to look into your work, to be honest I spend about 70% of my time at work lookign into your sheets, and a lot fo the steps you do are repetetive so I start picking them up oen by one. Obviously I am not able to write the codes by myself but I am learning the way you structure it. It is a great learning experience, I thank you for that as well.


    Do you actually sit down and write the codes from scratch or do you record your macros and then edit them?


    Thanks again for yoru help, I will be back in the forum tomorrow when I get to work (11am Pacific Time).


    Great Work!

    Re: automate daily tasks


    sevan17,


    Quote

    Do you actually sit down and write the codes from scratch or do you record your macros and then edit them?


    Depending on what I'm trying to do. I've finally gotten to the point that I know most of the generic xl objects and properties. However, if there is something I don't know well then recording is the best way to get xl to tell you what you need to do.


    As far as the splitting the project into pieces. That's the way to go. As you can accomplish 1 item that will then lead into the next. You only run into issues when asking for assistance whomever is helping doesn't know the end result ( like no duh really ). So when the code is written it's written for that one step.


    So what I'm going to do is this ( and it may take some time )
    1. Create a third file ( the file that will contain all the code )
    2. On it you will indicate the file names of both sheets
    3. It will then import both data files to seperate sheets
    4. Run code to summarize and sort
    5. Then on the second sheet you listed highlight according to your specifications.

    Re: automate daily tasks


    sevan17,



    Assuming that I understood what you wanted the attached workbook should do as you require ( at least thus far )

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login