Track Changes via Macro

[ManSim]

Hi all,
I am brandnew to VBA but learn a bit more every day. Now I have the following problem that needs your help:
I have about 15 different excel inventories and need to check every Monday if changes were made in the last week. All files are shared hence "tracking changes" is basically possible.
Now instead of clicking the mouse so many times I attempted to start recording a macro to ease my work. The result was the following code:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Sub TrackChanges()
'
' TrackChanges Macro
'
<o> </o>
'
With ActiveWorkbook
.HighlightChangesOptions When:="15.03.2010"
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = True
End With
End Sub
<o> </o>
<o> </o>
Unfortunately this macro does not really work - nothing happens! What is wrong with this?
<o> </o>
PS. At a later stage I want to replace the hard coded date with an input msgbox...

Much appreciate any comment.
Thanks in advance

 

[memoney77]

Ok, Thanks Mark. This will definitely get me going in the right direction. I'm sure it will be a great learning process in trying to tweak your code in line more with what I need. I will let you know how the API feeds work with the tracker next week when I am able to test. John

 

[arkusM]

John

Have a look here for more ideas:
http://www.mrexcel.com/forum/showthread.php?t=457494

 

[memoney77]

Mark, I was finanlly able to test the spreadsheet with the API feeds. Unfortunalty the tracker isn't recognizing cell value changes via an API feed. Any manual change that I make to the spreadsheet is getting tracked, so the macro is working. Do you have any suggestions in order to get the tracker to recognize changes from feeds? thanks, john

 

[arkusM]

Mark, I was finanlly able to test the spreadsheet with the API feeds. Unfortunalty the tracker isn't recognizing cell value changes via an API feed. Any manual change that I make to the spreadsheet is getting tracked, so the macro is working. Do you have any suggestions in order to get the tracker to recognize changes from feeds? thanks, john

Hey John,

I was wondering if you would have that problem. I say another thread on here(??) where someone was doing something similiar. I beleive the suggestion was to have the macro monitor an adjactent cell that is link to the API feed.
So API in Column S, if you can make Column T =S down your API feeds.

Then have the macro monitor col S. I don't have the abbility to test this code method but let me know if it works.

I beleive it has something to do with the way the API populates the data, sort of in the back ground if you will.

Let me know how it works out for you.

 

[memoney77]

I gave that a try too, but no luck. I set column "S" equal to column "Q". Q had the api feed, but it didn't recognize the change. I also removed the api feed from column "Q" and tried to manually change the value, but no luck. It seems the only time the tracker is working is when the specific cell value being monitored is manually changed. Have you had any luck in the tracker working for simple formulas? (ex. A1 = B1) where you change the value of B1 and the macro tracks the change in A1?

 

[arkusM]

I'll try some things. Is the API feed public data? if so post the link and I can try to figure something out.

 

[memoney77]

Sorry unfortunalty its not a public feed, but a paid service through my company. I'm sure if you can get the tracker to recognize any feed or cell value changes due to formulas it will work with our api too. Mark thanks again for your help on this, let me know if you come up with anything. John

 

[arkusM]

John,

1)How many rows are you trying to monitor?
2)how often to you need to check the differences?

What you are trying to do, now that I think about it is a different animal than my code was intened to do...
not to say what you are trying to do is not possiable but requires a different aproach.

 

[memoney77]

I'm trying to use the spreadsheet to automatically monitor security trades. For example, If an order is accepted the quantity on the online system is decreased (if the original quantity was 1,000 and someone purchased 100 the new quantity in the cell would be 900. The current quantity available is updated on my spreadsheet thru an api feed. Ideally I need to track those changes, so the decrease in 100 order would be multiplied by the profit column (which updates automatically too) to provide total profit or loss on the trade.

Total rows monitored currently 20, but may increase a little. and constant monitoring would be ideal, but if there is a 5 sec delay between updates it would still be ok.

The quantities are not changing all that often. A couple of changes an hour is about average, but depends on market conditions. So there is not an overwhelming amount of data.

Unfortunately my expertise is more financial than it is programming.

 

[arkusM]

I'm trying to use the spreadsheet to automatically monitor security trades. For example, If an order is accepted the quantity on the online system is decreased (if the original quantity was 1,000 and someone purchased 100 the new quantity in the cell would be 900. The current quantity available is updated on my spreadsheet thru an api feed. Ideally I need to track those changes, so the decrease in 100 order would be multiplied by the profit column (which updates automatically too) to provide total profit or loss on the trade.

Total rows monitored currently 20, but may increase a little. and constant monitoring would be ideal, but if there is a 5 sec delay between updates it would still be ok.

The quantities are not changing all that often. A couple of changes an hour is about average, but depends on market conditions. So there is not an overwhelming amount of data.

Unfortunately my expertise is more financial than it is programming.

Programming is not my expertise that is why helping you is taking so long!!

However, since the amount of rows being monitored is not huge and the fequency is accaptable at 5 seconds then I think I can get something to work for you. I post when I get something working.