Accessing Norgate data from Excel

[Lone Wolf]

I want to import price information from Norgatedata into Excel. Mainly for record keeping. I'd like to do it directly from Excel but there doesn't appear to be an option. There are three ways I can think of.

Option 1:
Use XLQ2 to directly access the data from Excel. XLQ2 costs $300. According to the Norgate website this appears to be the only way Excel can access the data. Is this true? Is share trade tracker using XLQ2? Isn't there a way to use VBA to access the data similar to the python API?

Option 2:
Use the Python interface. If I structure my spreadsheet in a standard format, I can have Python open it, look for the codes, return the prices from Norgate, update the price fields and exit the spreadsheet. Seems like roundabout way to do a simple task. But it's free and automatic, once you've put the effort into making it work.

Option 3:
Have Excel export a list of relevant tickers. Import that list into Amibroker as a watchlist and have Amibroker run an explore to produce a list of closing prices as of the specified dates. Copy and paste that list back into Excel as a table and reference the data as required. A manual process, but in some ways the easiest method.

Does anyone have a better idea?

 

[MovingAverage]

I want to import price information from Norgatedata into Excel. Mainly for record keeping. I'd like to do it directly from Excel but there doesn't appear to be an option. There are three ways I can think of.

Option 1:
Use XLQ2 to directly access the data from Excel. XLQ2 costs $300. According to the Norgate website this appears to be the only way Excel can access the data. Is this true? Is share trade tracker using XLQ2? Isn't there a way to use VBA to access the data similar to the python API?

Option 2:
Use the Python interface. If I structure my spreadsheet in a standard format, I can have Python open it, look for the codes, return the prices from Norgate, update the price fields and exit the spreadsheet. Seems like roundabout way to do a simple task. But it's free and automatic, once you've put the effort into making it work.

Option 3:
Have Excel export a list of relevant tickers. Import that list into Amibroker as a watchlist and have Amibroker run an explore to produce a list of closing prices as of the specified dates. Copy and paste that list back into Excel as a table and reference the data as required. A manual process, but in some ways the easiest method.

Does anyone have a better idea?

This isn't a direct answer to your question, but have you asked the folks at Norgate? They're very responsive and they have been super helpful for me in the past when trying to do similar things. Good luck.

 

[Remickz]

What about not modifying your current spreadsheet, but have a new simplified sheet for inputs and outputs that python accesses, and then a macro that adds that fresh data to your current dataset?

 

[Joe90]

I use a function in Norgate Data Updater to export stock data in txt.csv files to a folder (where txt is the stock code). Look under tools\export task manager. https://norgatedata.com/ndu-tools.php
As I've never learnt to code VBA, I wrote a simple highlevel process description. It cost me $35 on fiverr to have someone write the VBA code for a macro to look up the stockcode.csv file and extract the price field into my trading spreadsheet.

 

[Remickz]

I use a function in Norgate Data Updater to export stock data in txt.csv files to a folder (where txt is the stock code). Look under tools\export task manager. https://norgatedata.com/ndu-tools.php
As I've never learnt to code VBA, I wrote a simple highlevel process description. It cost me $35 on fiverr to have someone write the VBA code for a macro to look up the stockcode.csv file and extract the price field into my trading spreadsheet.

I'm sure there would be plenty of people here who can help with VBA if you need it in the future.

 

[Lone Wolf]

This isn't a direct answer to your question, but have you asked the folks at Norgate? They're very responsive and they have been super helpful for me in the past when trying to do similar things. Good luck.

Thanks for the response. I emailed them just this morning. I'll report back with anything valuable.

What about not modifying your current spreadsheet, but have a new simplified sheet for inputs and outputs that python accesses, and then a macro that adds that fresh data to your current dataset?

Yes, I expect I'll end up doing it that way regardless of which option I go for. I'll have a "data tab". Then as you say, the other sheets can pull from that tab. For now I can populate it manually from Amibroker until I'm suitably motivated to dust of my Python skills enough to automate it.

I already use other software for portfolio performance. I'm just looking to have basic records on the side to help cross-check the main records.

I use a function in Norgate Data Updater to export stock data in txt.csv files to a folder (where txt is the stock code). Look under tools\export task manager. https://norgatedata.com/ndu-tools.php
As I've never learnt to code VBA, I wrote a simple highlevel process description. It cost me $35 on fiverr to have someone write the VBA code for a macro to look up the stockcode.csv file and extract the price field into my trading spreadsheet.

Thanks for the reminder Joe, I had found this function ages ago and forgot about it. I was hoping not to do a two step process, but this is another option.

 

[Lone Wolf]

They just responded to my email:

We do not have an Excel plugin that directly connects to the NDU database. XLQ2 is the only way to access the data inside Excel (other than perhaps using exported CSV files).

10 out of 10 for their customer service response time. Boo out of 10 for the restricted access to the data.

 

[MovingAverage]

The more I think about it the more I'm surprised there's no Excel plugin. You can do some really great statistical analysis with Excel that is useful for stocks and I'm surprised there isn't already a plugin.

 

[Lone Wolf]

The more I think about it the more I'm surprised there's no Excel plugin. You can do some really great statistical analysis with Excel that is useful for stocks and I'm surprised there isn't already a plugin.

They might want to limit easy access to the data so they can charge companies a fee to include Norgate access in their products. The developers of XLQ2 probably wouldn't like people getting free access to Norgate via Excel.

 

[ajcode]

Possible Option 4: Since Norgate provide a DLL plugin only for use in Amibroker, maybe the DLL functions from this same plugin could be called from Excel macros to gain access to the data. Probably not, but if not, maybe Norgate could in future provide a generic DLL for people that don't use Amibroker (e.g. EXCEL).

If you pay for the data provided by Norgate, I don't see why you should have to pay for Amibroker to access it through a DLL provided by them only for Amibroker. It shouldn't matter what software package you use, if a free DLL is available to Amibroker users, then they should make it available to all programming languages that can call DLL functions. This would be of great benefit and allow users to access the data in a timely, efficient and professional manner. Why should you have to pay for third party software to access the data professionally from EXCEL if you've paid for a DATA subscription from Norgate?

 

[Lone Wolf]

Possible Option 4: Since Norgate provide a DLL plugin only for use in Amibroker, maybe the DLL functions from this same plugin could be called from Excel macros to gain access to the data. Probably not, but if not, maybe Norgate could in future provide a generic DLL for people that don't use Amibroker (e.g. EXCEL).

Good thought, but I wouldn't even know where to begin. And for what I want it's not worth delving that deep into. I ended up just keeping an Amibroker watchlist with all my open trades. Once a week I update the list, run an explore to generate the data and copy it into excel.

If you pay for the data provided by Norgate, I don't see why you should have to pay for Amibroker to access it through a DLL provided by them only for Amibroker. It shouldn't matter what software package you use, if a free DLL is available to Amibroker users, then they should make it available to all programming languages that can call DLL functions. This would be of great benefit and allow users to access the data in a timely, efficient and professional manner. Why should you have to pay for third party software to access the data professionally from EXCEL if you've paid for a DATA subscription from Norgate?

I agree, it's not ideal. I'm sure they have their reasons. You can export the data as CSV, and there's the python API, so it's not like they're trying to keep it locked up completely. They just seem to want control over which third-party programs have access to the data. I have a few programs I've accumulated over the years that I can't use as they aren't supported. I'd have to buy the old Premium Data to get it in metastock format. I guess part of the problem is that they don't have much competition.

 

[DaveDaGr8]

I found the easiest, best and quickest way is to go in via a network socket the same way as python.

I wrote a DLL last year that can interrogate pretty much all of the norgate database.
Some issues though --
You have to copy over my DLL, so there's security issues ( from your end).
You still need to do a bit of VBA coding.


The other way, if you just want 20 min delayed data through excel is through their new data types ( provided you have an office 365 account )

https://www.howtoexcel.org/general/data-types/

It's awesome and straightforward and gives you some pretty rich information. You don't get norgates watchlists or historic data though so it's not as good for analysis, but perfect for a trade tracker.

 

[Lone Wolf]

I found the easiest, best and quickest way is to go in via a network socket the same way as python.

I wrote a DLL last year that can interrogate pretty much all of the norgate database.
Some issues though --
You have to copy over my DLL, so there's security issues ( from your end).
You still need to do a bit of VBA coding.

Hi Dave,

I'll give the DLL a skip. I don't want to dive too far down the rabbit hole now that I have a solution I'm happy with. But thanks for letting me know such a thing is possible.

The other way, if you just want 20 min delayed data through excel is through their new data types ( provided you have an office 365 account )

https://www.howtoexcel.org/general/data-types/

It's awesome and straightforward and gives you some pretty rich information. You don't get norgates watchlists or historic data though so it's not as good for analysis, but perfect for a trade tracker.

This is amazing, thank you. I wasn't aware of that. On historic data - No you don't get all the Norgate info, but you do get historical prices via stockhistory(). The following for example will list all weekly closing prices for BHP since the start of the year:
=STOCKHISTORY("XASX:BHP", "01/01/2021", TODAY(), 1)

One quick tip on the weekly format, it shows the date of the first trading day of the week, but the price is for the close of that week. So in the first example above, BHP didn't trade on Monday 28-12-20, so it shows 29-12-20. And the price of $42.43 is from the close of that week.
More here:
https://techcommunity.microsoft.com/t5/excel-blog/announcing-stockhistory/ba-p/1404338

 

[jogibains]

Good thought, but I wouldn't even know where to begin. And for what I want it's not worth delving that deep into. I ended up just keeping an Amibroker watchlist with all my open trades. Once a week I update the list, run an explore to generate the data and copy it into excel.



I agree, it's not ideal. I'm sure they have their reasons. You can export the data as CSV, and there's the python API, so it's not like they're trying to keep it locked up completely. They just seem to want control over which third-party programs have access to the data. I have a few programs I've accumulated over the years that I can't use as they aren't supported. I'd have to buy the old Premium Data to get it in metastock format. I guess part of the problem is that they don't have much competition.

Have you try Data from Refinitiv DataLink.

https://www.metastock.com/products/endofday/reutersdatalink/

 

[jogibains]

Good thought, but I wouldn't even know where to begin. And for what I want it's not worth delving that deep into. I ended up just keeping an Amibroker watchlist with all my open trades. Once a week I update the list, run an explore to generate the data and copy it into excel.



I agree, it's not ideal. I'm sure they have their reasons. You can export the data as CSV, and there's the python API, so it's not like they're trying to keep it locked up completely. They just seem to want control over which third-party programs have access to the data. I have a few programs I've accumulated over the years that I can't use as they aren't supported. I'd have to buy the old Premium Data to get it in metastock format. I guess part of the problem is that they don't have much competition.

Have you Try data from Refinitiv DataLink.

https://www.metastock.com/products/endofday/reutersdatalink/

 

[Lone Wolf]

Have you Try data from Refinitiv DataLink.

https://www.metastock.com/products/endofday/reutersdatalink/

Thanks for the suggestion. But no I haven't. Norgate has the features I need, works well with Amibroker and I'm familiar with it. Not being able to access the data from excel isn't a deal breaker for me, and certainly isn't worth swapping to or buying additional data.