Search in uioop.blogspot.com

Wednesday, January 27, 2016

Check Multiple Criteria with Excel INDEX and MATCH

Article from: http://blog.contextures.com/archives/2012/07/12/check-multiple-criteria-with-excel-index-and-match/

The INDEX function can return a value from a range of cells, and the MATCH function can calculate a value's position in a range of cells.
For example, in the screen shot below, cell A7 contains the item name, Sweater:
  • the MATCH function can find "Sweater" in the range B2:B4. The result is 1, because "Sweater" is in the first row of that range.
  • the INDEX function can tell you that in the range C2:C4, the first row contains the value 10.
So, by combining INDEX and MATCH, you can find the row with "Sweater" and return the price from that row.
=INDEX($C$2:$C$4,MATCH(A7,$B$2:$B$4,0))
indexmatchprice01

Find a Match for Multiple Criteria

In the previous example, the match was based solely on the Item name – Sweater. Sometimes life, and Excel workbooks, are more complicated.
In the screen shot below, each item is listed 3 times in the pricing lookup table. To get the right price, you'll need to specify both the item name and the size. We want to find the price for a large jacket.
indexmatchmulticriteria00

Does it MATCH? True or False

Instead of a simple MATCH formula, we'll use one that checks both the Item and Size columns. To do something similar on a worksheet, we could add columns to check the item and size columns.
  • If the Item in column B is a Jacket, the result in column E is TRUE. If not, the result is FALSE
  • If the Size in column C is Large, the result in column F is TRUE. If not, the result is FALSE
In column G, when you multiply the TRUE/FALSE values, the result is 1, only if both are TRUE.
indexmatchmulticriteria03
We could use a MATCH formula to find the position of a 1 in column G, in the screen shot above. The 8th row of data (worksheet row 9), has the 1, and that row will give us the correct price for a large jacket.

Use MATCH With Multiple Criteria

Instead of adding extra columns to the worksheet, we can use an array-entered formula to do all the work. Here is the formula that we'll use to get the correct price, and below is the explanation:
=INDEX($D$2:$D$10,
MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))
NOTE: This is an array-entered formula, so press Ctrl + Shift + Enter, instead of just pressing the Enter key.
In this example,
  • prices are in cells D2:D10, so that is the range that the INDEX function will use.
  • item name is in cell A13
  • size is in cell B13.
The formula checks for the selected items in $B$2:$B$10, and sizes in $C$2:$C$10. The results are multiplied.
  • (A13=$B$2:$B$10)*(B13=$C$2:$C$10)
The MATCH function looks for the in the array of results.
  • MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0)
If you select that part of the formula and press the F9 key, you can see the calculated results. In the screen shot below there are 9 results, and all are zero, except the 8th result, which is 1.
indexmatchmulticriteria04
So, the INDEX function returns the price – 40 – from the 8th data row in column D (cell D9).
indexmatchmulticriteria01
To find the product code for the selected item and size, you would change the formula to look in cells A2:A10, instead of the price column.
=INDEX($A$2:$A$10,
MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))
In this example, the product code would be JK003, from cell A9.

Tuesday, January 26, 2016

Excel Formula: Compare Column A and B in Sheet1 and Sheet2, If matches copy Column C and Column D data from Sheet1 to Sheet 2

Compare Sheet1.Column A with Sheet2.Column A, If matches then compare Sheet1.Column B with Sheet2.Column B. If matches copy Sheet1.Column C and Sheet1.Column D data to Sheet2.Column E and Sheet2.Column G.

Sheet1




Sheet2


Result


Formula: 
=IF(AND(COUNTIF(Sheet2!A2,Sheet1!A2),(COUNTIF(Sheet2!B2,Sheet1!B2))),Sheet1!C2,0)

Compare Two or More Lists in Excel with IF and COUNTIF

Article from: http://www.bighungrygeek.com/compare-two-or-more-lists-in-excel-with-if-and-countif


As I previously discussed, Excel provides many useful ways to automatically compare two lists of data or information. In our other example we compared two lists of four digit account values; for this example we’ll compare two lists of names.
You and a friend are throwing an epic party and have each maintained separate guest lists to track who’s coming. After a few weeks you decide to compare both lists to make sure everyone on your friend’s list (List B) is also on your list (List A).

How to do it

  1. Arrange the lists in two columns with List A in column A and List B in column B.
  2. Create a third column in column D called List C (leave column C blank for easier readability).
  3. In cell D5 enter the formula: =IF(COUNTIF(B:B,A5),A5,0) and press Enter.
    Enter formula into cell D5
    Enter formula into cell D5.
  4. Select cell D5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Equal To…
  5. In the Equal To dialog box, type 0 and click OK.
  6. Select cell D5 down to the end of the lists and press Ctrl+D to copy the formula and conditional formatting down.
    Copy the formula down to the end of the longer list (List B in this example).
    Copy the formula down to the end of the lists.

The Result

A name in List C means the corresponding name from List A was also found within List B. In the screenshot above, cell D5 displays the “Moon Barrientos,” which means that name was found on both List A and B. On the other hand, cell D12 displays Red Zero which means the corresponding name from List A (in this case, “Aracely Rock”) does not exist within List B.
This example demonstrates how to determine if a particular value – in this case a name – in List A also exists within List B. To find the opposite – whether a name in List B also exists within List A – simply change the formula in cell D5 to: =IF(COUNTIF(A:A,B5),B5,0) and press Enter. Then copy that formula down to the end of the lists.

How & Why it Works

Our formula makes use of two functions, IF and COUNTIF. The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. COUNTIF counts the number of cells within a range that meet the given condition.

The Formula

=IF(COUNTIF(B:B,A5),A5,0)
COUNTIF has two required arguments, or inputs, to work:
  • range (where should Excel look, B:B in our example)
  • criteria (what should Excel find, A5 in our example)
COUNTIF checks column B (using B:B checks the entire column) for the name in cell A5. If it finds the name (i.e. the criteria you specified was met) it returns 1, otherwise it returns 0. NotewhenCOUNTIF returns 1 or 0, Excel treats that as TRUE (1) or FALSE (0).
The IF function has three required arguments, or inputs, to work:
  • logical_test (any value or expression that can be evaluated to TRUE or FALSE)
  • [value_if_true] (what value should Excel return if if logical_test is TRUE)
  • [value_if_false] (what value should Excel return if logical_test is FALSE)
In our example COUNTIF is the logical_test that gives the IF function either a 1 or 0, depending on whether it finds the name we specified from cell A5 within column B. If it finds the name (i.e. thelogical_test is TRUE), we specified A5 as the [value_if_true] so Excel will display that name and move on. If it does not find the name (i.e. the logical_test is FALSE), we specified 0 as the[value_if_false], so Excel will display 0 and move on.
You can quickly see a list of names missing from List B by filtering List C to show only “0.” The conditional formatting just makes it easier to spot the missing values.

Thursday, January 14, 2016

Improve MT4 Trade Copier speed on multi-core CPU machines

Article from: https://www.mt4copier.com/improve-mt4-trade-copier-speed/

In this blog post I’ll show how to improve MT4 performance and how to make sure your Expert Advisors and indicators are not suffering from unnecessary delays. This literally allows you to speed up MT4 software when it is used on multi-core processor VPS servers. In this example I will use one of my MT4 apps to demonstrate how to identify this problem and how to fix this, but you might experience the same problem with any other MT4 app in general.
Recently I have been receiving quite a lot of emails where people complaint that my Local Trade Copier software is working very slowly. Results have been quite different for most of those trade copier users, but on average they have experienced somewhere between 10 to 60 second delay when copying trades. That is huge and not tolerable and should be fixed. However, what I found out is that everything is okay with my software and the problem comes from the MT4 platform when it runs on a multi-core CPU machine (in most cases VPS servers). Normally the trade copier software should synchronize trades every 400 milliseconds, which is basically 2 times every second, and you can even adjust the sync rate to make it work even faster. I had this “slow copier” problem as well in the past so I want to share with you how to solve this problem.
This “slow MT4” problem is quite well known to programmers and there are lots ofdiscussions about MT4 and multi-core processors. However, I do not want to throw technical terms and explanation at you. I just want to show you how to solve the problem when your MT4 is running slowly. And even if your MT4 does not seem to run slowly, by applying this trick you might even be able to run more MT4 client terminals on the same VPS server.
Technically, the problem is that MT4 is a 32-bit application and this gives trouble to work efficiently on multi-core processor machines (like Dual-core, Quad-core, etc). However, in most cases this seems to be the problem only when you are running multiple MT4 client terminals on the same VPS server and this server has multi-core CPU. Usually, the multi-core CPU on a VPS server is a “virtual CPU”, which means those CPU cores are not real physical CPU’s. They are simulated virtual CPU cores.
If you are running a single MT4 instance you should not face problems like this. This should not be a problem as well if you are running multiple MT4 instances on the VPS server that has only single CPU core. If you are running some simple MT4 app like thisPartial Close EA then you might not face these problems as well.

How many CPU cores your VPS server has?

Here’s how to know how many CPU cores your VPS server has.
Click the right-mouse button somewhere on the task-bar at the bottom of your Windows screen and in the pop-up menu choose “Start Task Manager“.
You can start Task Manager in Windows VPS server from this pop-up menu.
You can start Task Manager in Windows VPS server from this pop-up menu.
When the “Task Manager” window loads up you need to switch to the “Performance” tab where you’ll see if you have multiple CPU cores or not.
Performance tab with 4x CPU cores in the Windows Task Manager.
Performance tab with 4x CPU cores in the Windows Task Manager.
In the picture above you can see this VPS server has 4x CPU cores.
The specifications of this VPS server are as follows:
  • AMD Opteron(tm) Processor 6136 (4 CPUs) ~2.4Ghz.
  • 8GB RAM Memory.
  • VPS system runs on a VMWare Virtual Platform.
If you switch to the “Processes” tab, you’ll find terminal.exe’s in the process list. This is your MT4 processes and what you need to do is to set each of them to use the same CPU core. By default each process use all CPU cores and in our case this creates a problem for MT4.
MT4 terminal.exe process list in the Windows Task Manager.
MT4 terminal.exe process list in the Windows Task Manager.
To make the process more simple I suggest to sort that process list by name by clicking on the column that says “Image Name“.
In this Task Manager process list we see that there are 4x MT4 instances running. Each of those MT4 platforms run several MT4 Copier EA instances which makes a total of 9x EA’s running on this VPS. CPU Usage in the picture is shown as 19% but it is actually jumping between 19% and 36% all the time. At a first glance anyone might say that CPU load of 36% or even 60% is not a big deal and VPS should work fine, however this “CPU Usage” number seems to be incorrect in such case.
The whole VPS system is working very slowly and it is even difficult to switch to another window. If I try to switch to another MT4 platform window I have to wait about 20-60 seconds. Obviously trade copying on such server would take ages and you would experience huge delays and price differences. I even had the same problem with only 2x MT4 instances and only two copier EA’s (Server EA and Client EA).

Simple trick to make MT4 run faster on multi-core CPU servers

Now here’s a simple trick you need to apply to make MT4 client terminals (and of course all apps running inside these terminals) work faster. There are two options. For some, option #1 helps speed up MT4, while others find only option #2 is actually helping.

Option #1: Set all terminal.exe processes to use the same CPU core.

To apply option #1 you need to set all terminal.exe processes to use the same CPU core. It does not really matter which CPU core you’ll choose to use, what’s important is that all MT4 processes should use the same. To make it simple I always set all terminal.exe’s to use CPU0. Do not worry that this will put all load on one CPU core and Windows Task Manager will show 100% load on CPU0. Remember, those are virtual cores that are actually running on the same physical processor at your hosting provider’s server. So actually all work will still get done by that physical CPU, but because MT4 will not need to jump and split operations between multiple CPU cores it will work faster.

Option #2: Set each terminal.exe process to use different CPU core.

If option #1 does not help you should also try option #2. To apply option #2 you need to set each terminal.exe process to use different CPU core. Say your VPS has 4x CPU cores and you run 4x MT4 instances. Then all 4x MT4 instances should be set as follows:
  • MT4 instance #1 should use CPU0
  • MT4 instance #2 should use CPU1
  • MT4 instance #3 should use CPU2
  • MT4 instance #4 should use CPU3
If you have more MT4 platforms than CPU cores you should close the rest of MT4 instances (at least for testing purposes) to see if this option #2 solution helps you at all.

How to set MT4 to use certain CPU core

Configuring MT4, or any other process from the list, to use only certain CPU cores is quite easy. You need to switch to the “Processes” tab, choose the terminal.exe process from the list, click the right-mouse button on that item and choose “Set Affinity” from the pop-up menu. In the “Processor Affinity” window you need to deselect all CPU cores and select only CPU0 like in the picture below.
Setting CPU affinity on MT4 terminal process in the Windows Task Manager.
Setting CPU affinity on MT4 terminal process in the Windows Task Manager. This makes the MT4 platform to work only using single CPU core for faster performance.
When I did this for all 4x MT4 instances I am running on my VPS all of them started to work efficiently. I can now switch between windows easily and CPU Usage dropped as well to 16% – 29% (even though I still think this number does not show correct values on VPS servers).
Obviously I can copy trades at the fastest speed possible. Trades are transferred between MT4 platform in under 1 second now.

MT4 Trade Copier speed is just one piece of the puzzle

I would mention that Local Trade Copier speed has little to do with how fast your trades are copied between MT4 platforms. In most cases delays come from the problems like slow MT4 (like in this example), poor internet connection or even execution delays from your broker. There were even cases when brokers delay trade execution on purpose for some economic news scalpers/traders.
To list all potential problems that affect trade copying speed I would list them in the following order (where 1st item is the most common problem and the last item is the least common problem):
  1. Slow MT4 performance because of the multi-core CPU server.
  2. Slow MT4 performance because of too many 3rd party indicators or Expert Advisors running on the MT4 terminal.
  3. Too many MT4 client terminals running on the same VPS server.
  4. Slow VPS performance because of 3rd party software (anti-virus, firewall, Internet browsers and other apps).
  5. Poor internet connection. I know all VPS providers will say that their connection is high quality, but even world class services have issues from time to time. Keep this in mind. On the other hand, it’s not necessary your server provider is the one to blame for bad Internet connection. There can be several or even dozen of servers/gateways in between your VPS and your broker’s server.
  6. Your MT4 app problem (in this example that would be the Local Trade Copier).
I won’t deny that my MT4 apps or MT4 copier might have problems running on your MT4 or your VPS. This have happened in the past and I did a lot of work to fix this. However, based on the last several months and tens of solved “slow trade copying” cases I’ve noticed that it’s always MT4, VPS or Internet connection problem. When you finally fix the problem with the trade copying speed you can even try to copy your Forex trades at a better price.

How to know if your LTC copier is slow

So how to know if it’s the Local Trade Copier working slow and does not copy trades fast enough or is it that the problem lies in the slow execution from your broker?
That’s quite simple. Both, Server EA and Client EA has special counters in the blue dashboard that are growing on each synchronization cycle. Server EA has “Server send” counter and Client EA has “Server read” counter.
Server send counter in the blue dashboard of the LTC Server EA.
“Server send” counter in the blue dashboard of the LTC Server EA.
If counters on all LTC EA instances are growing steadily, consistently and rapidly (according to your SyncRate setting in the EA Inputs), then it looks like your copier is working fine. If you see one of those counters growing slow and randomly, you should apply option #1 and/or option #2 solutions explained earlier in this article.
If counters on all LTC EA’s are growing steadily and consistently then you should also check the execution time. After each copied trade Client EA will print information in the “Experts” tab at the bottom of MT4 client terminal about the trade execution speed, price deviation and slippage.
Local Trade Copier reports broker execution speed on copied trades.
Local Trade Copier reports broker execution speed on copied trades.
In the picture above you can see Client EA reported that it took 203ms to execute the trade. This obviously does not include the synchronization time (the time required for the copier to actually send the signal from master to the client account). These 203ms is the time spent to deliver the “open trade” request to the broker’s server, actually get the trade executed and receive a confirmation answer from the broker’s server. In other words, this is out of control for the trade copier software or any other MT4 app. This depends on how fast your Internet connection is and how fast your broker’s execution speed is.
Under normal conditions you should get execution delay somewhere between 50-300 ms. If you want to get this close to 1ms, or something like that, you should rent a VPS server which is on the same network as your broker’s server. You can contact your broker to find out such information.
Local Trade Copier reports price deviation and slippage on copied trades.
Local Trade Copier reports price deviation and slippage on copied trades.
In the picture above we also see that the copier reported actual price deviation on the copied trade comparing it to the original trade’s open price. In this case there was a price difference of 0.6 pips which is considered very low under such circumstances. This report also included slippage which was zero in this case. Slippage is something you cannot avoid, but first thing what you need to find out is whether the slippage you get is the result of a bad Internet connection or it’s your VPS server running slowly.

Trade copier speed troubleshooting checklist

  1. After each trade is copied there will be a message logged in the “Experts” tab which gives the exact slippage and delay in ms. This way you can know if delay is coming from the broker/internet connection. If you get execution delay between 50ms and 300ms then your Internet connection and broker is working fine.
  2. Check if “Server read” and “Server send” counters are growing steadily or randomly. If one of these counters grow randomly, with delays, then it means your computer is slowing down the trade copier software.
  3. Check if your computer/VPS server has multiple CPU cores or just a single CPU core. If you have a single CPU then try closing 3rd party applications and remove other EAs and indicators from your MT4 to make it work faster. If this does not help then maybe you are running too many MT4 instances and/or simply need to get a better and more powerful computer.
  4. If your computer/VPS server has multiple CPU cores then you might suffer from the well known MT4 issue that it cannot work efficiently on multiple CPU core machines. In that case you would have to set all terminal.exe’s to use the same CPU. This can be done from the “Processes” tab in the “Task Manager” window. You need to select each terminal.exe and choose “Set Affinity“. Then in the CPU list window you need to select which CPU’s each process should use. Idea here is to try two operational modes: 1) Set all terminal.exe’s to use the same CPU0; 2) Set each terminal.exe to use different CPU.
If you have tried all that above and still have slow trade copying speed you should contact me.

Conclusion

If your MT4 is running slowly and your VPS server provider tells you that everything is okay on their side, you should check if your VPS is a multi-core CPU machine. MT4 in some cases cannot work efficiently on multi-core CPU machines and requires some additional configuration. I hope that this article will help you find out the cause of your slow MT4 problem and possibly fix it.
So, have you suffered from this problem before? Or maybe you are still struggling to find a solution to this? If you tried all that, did it helped? Please post your comments below.

AVG Internet Security 2013

Total Pageviews

Contributors