Excel 2010 x64

syswizard · Dec 20, 2011

Quote from bs2167:

Interested in any thoughts in general on this...properly set up, how low latency can Excel 2010 go on a large portfolio?

Well, to do it, you've got to shut down a lot of automatic facilities and do them on a timer instead.
Make the calculations update manually, lock the worksheet, kill the screen updating, etc.
Once you do that, it's pretty fast. Its automatic the event handling that really slows it down.

jtrader33 · Dec 20, 2011

Quote from syswizard:

Doubtful...mainly because none of the 2010 speed increase is due to improvements in VBA....it's all in the formulas. VBA remains STA (single threaded architecture). Relative to Java and Dot-Net, it's slow.
You are talking 2-3 milliseconds per symbol....that's pretty fast.

Yeah, I was thinking of an approach that minimizes the use of VBA and tries to use 2010's strength by allowing the formulas to do all the work (it must be a horrible approach b/c it's pretty much the exact opposite of what your saying, ha). The only macro in the sheet is one that is event driven by a single cell that flags to TRUE when there is an order to be sent from any of the models. Then it can run a quick order send / risk check macro, but then right back to the sheet on full auto-calc. The downside to the approach is that any signal logic must be embedding in cells creating the need for any historical bars, stats, etc. to be held in formulas as well. My concern with that approach is losing ticks while copying data around for bars or when the order sending macro is running. I don't know the answer, but I would imagine that data gets lost rather than queue??

WinstonTJ · Dec 20, 2011

you need to be using something other than Excel

mokwit · Dec 20, 2011

If I write something in Excel 2003 32 bit and then open it in 10 64 bit and then save as xl10 64 bit do i then get all the benefits of 10 64 bit? - reason is I prefer to do development in 2003 with old interface I am used to and have been using for years.

syswizard · Dec 21, 2011

Quote from mokwit:

If I write something in Excel 2003 32 bit and then open it in 10 64 bit and then save as xl10 64 bit do i then get all the benefits of 10 64 bit? - reason is I prefer to do development in 2003 with old interface I am used to and have been using for years.

Yes, just save the workbook as XLSX or XLSM format. Unfortunately you'll need to get used to the "Ribbon" interface. However, someone did create an addin that provides the old style menu bar system in XL 2007, 2010. You should be able to Google it.
The VBA menues are the same.

J.P. · Dec 21, 2011

Quote from syswizard:

Yes, just save the workbook as XLSX or XLSM format. Unfortunately you'll need to get used to the "Ribbon" interface. However, someone did create an addin that provides the old style menu bar system in XL 2007, 2010. You should be able to Google it.
The VBA menues are the same.

I've found the most efficient format for 64-bit Excel to be .xlsb. Try it, you'll see.

syswizard · Dec 22, 2011

Quote from J.P.:

I've found the most efficient format for 64-bit Excel to be .xlsb. Try it, you'll see.

Interesting. Have any links to prove or confirm that claim ?

GTS · Dec 22, 2011

Quote from syswizard:

Interesting. Have any links to prove or confirm that claim ?

http://blogs.msdn.com/b/davbosch/archive/2006/08/29/730183.aspx

Even though we've done a lot of work to make sure that our XML formats open quickly and efficiently, this binary format is still more efficient for Excel to open and save, and can lead to some performance improvements for workbooks that contain a lot of data, or that would require a lot of XML parsing during the Open process.

http://blogs.msdn.com/b/dmahugh/archive/2006/08/22/712835.aspx

With the upcoming release of the 2007 Microsoft Office System, much attention has been focused on the new Open XML file formats. But there is also another new file format for Excel spreadsheets that you'll want to take a look at if you're building unusually large or complex spreadsheets: the new XLSB binary format

One more thing to consider before jumping to 64bit Excel, compatibility:

http://technet.microsoft.com/en-us/library/ee681792.aspx

Disadvantages

The following issues might affect compatibility:

Microsoft Access MDE/ADE/ACCDE files Databases that have had their source code removed (such as .mde, .ade, and .accde files) cannot be moved between 32-bit and 64-bit editions of Office 2010. Such databases that are created by using 32-bit Office (any version) can be used only with 32-bit Office, and a database that is created on 64-bit Office can be used only on 64-bit Office.

ActiveX controls and COM add-ins ActiveX controls and add-in (COM) DLLs (dynamic link libraries) that were written for 32-bit Office will not work in a 64-bit process. As a result, Office 2010 64-bit solutions that try to load 32-bit ActiveX controls or DLLs will not work. Installations of 64-bit Office 2010 will run only 64-bit controls. Computers can have 64-bit and 32-bit controls installed, and Office 2010 64-bit can only run the 64-bit versions of the controls. The workaround for resolving these issues is to obtain 64-bit compatible controls and add-ins or to install Office 2010 32-bit (WOW).

In addition to controls that load into Office applications, there are also Web-based solutions that use ActiveX controls in Microsoft Internet Explorer. Office 2010 64-bit editions install some Office 32-bit client-side controls for supporting solutions in a 32-bit browser (the default browser on current 64-bit Windows systems). The Edit in Datasheet view functionality is not supported if you install 64-bit Office 2010. However, the functionality is available if you install 32-bit Office 2010.

In-place activation The following issues might occur if there is not a match between the bitness of Office 2010 and registered applications:

An OLE server might not instantiate in place and might fail to open if the application registered is not the same bitness as the version of Office installed. (For example, if the OLE Server application is 32-bit and the version of Office installed is 64-bit.)

Inserting an object into an Office 2010 application document might fail in cross-bitness scenarios. (For example if you insert a 32-bit object in a 64-bit Office 2010 application document.)