excel
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
excel [2018/07/13 12:25] – moved link su | excel [2023/05/02 12:16] – [Troubelshooting] su | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Excel Query Add-In ====== | ====== Excel Query Add-In ====== | ||
- | The Excel Query Add-In allows you to query your data sources directly from Excel. | + | The Excel Query Add-In allows you to query your data sources directly from Excel. You can query data by using the "IP Data Query" tab or by writing data query formulas. |
- | ==== Getting Started ==== | + | There are some instructional videos demonstrating how to use the Excel Add-In in [[https:// |
- | === Setup === | + | You can download a spreadsheet containing examples of all the formulas [[https:// |
- | - Download the Excel Query Add-In from the [[https:// | + | ===== Contents ===== |
- | - Run the installer | + | |
- | - The Add-In will launch automatically (skip step 1 in " | + | |
- | === Running the Add-In === | + | * [[excel: |
+ | |||
+ | * [[excel: | ||
- | | + | |
- | - You may be asked if you trust the Add-In, select either " | + | |
- | - An App Store login window will open in Excel, sign in with your App Store account | + | |
- | - When prompted to authorize the application click "Allow Access" | + | |
- | - Authorize your data sources by visiting | + | |
- | |Authorized Applications]]. Click " | + | |
- | //You can download a spreadsheet containing examples of all the formulas | + | * [[excel:formulas|Writing Data Query Formulas]]: Help writing data query formulas. |
- | ==== IP Data Query UI ==== | + | ===== Troubleshooting ===== |
- | === Get Current Value === | + | Local logs are available in: |
- | Clicking "Get Current Value" allows you to configure a request to get the current value for some tags. | + | LOCALAPPDATA}/ |
- | * Select the relevant data source | + | |
- | * Enter a filter to search for tags available from that data source and click " | + | |
- | * Select the tag(s) you would like to query | + | |
- | * Select whether you would like a header row and time stamps | + | |
- | * Click " | + | |
- | This will insert the result of the query you entered at the currently selected point in the spreadsheet. | ||
- | == Advanced == | ||
- | You can use the " | ||
- | |||
- | Deselect "Enter As Formula" | ||
- | |||
- | === Get Historical Values === | ||
- | |||
- | Clicking "Get Historical Values" | ||
- | * Select the relevant data source | ||
- | * Enter a query to search for tags available from that data source | ||
- | * Select the tag(s) you would like to query | ||
- | * Enter the start, end times, interval and data funciton for the query | ||
- | * Enter the number of points (e.g. a 10 day query with interval 1h is 240 points) | ||
- | * Select whether you would like a header row and time stamps | ||
- | * Click " | ||
- | |||
- | This will insert the result of the query you entered at the currently selected point in the spreadsheet. | ||
- | |||
- | == Advanced == | ||
- | |||
- | You can use the " | ||
- | |||
- | Deselect "Enter As Formula" | ||
- | |||
- | === Refresh Data === | ||
- | |||
- | This button will reload all data query formulas on the current worksheet. | ||
- | |||
- | ==== Using Data Query Formulas ==== | ||
- | |||
- | The Excel Query Add-In adds multiple Excel formulas that can be used to make queries. | ||
- | |||
- | The formulas added by the Add-In are Excel array formulas, this means that the result of the formula occupies multiple cells. | ||
- | |||
- | Before entering a formula select the range that the results of the query should occupy (if you don't know how many results there will be select a larger area, or a whole column, otherwise the results will be truncated to fit in the selected area). | ||
- | |||
- | Once you have written (or edited) a formula press Ctrl + Shift + Enter to update the effected range, if you forget to do this you will get a "You can't change part of an array" error from Excel. | ||
- | |||
- | If a cell contains "# | ||
- | |||
- | === Get Available Data Sources === | ||
- | |||
- | You can get the data sources available to the Add-In by using the " | ||
- | |||
- | * Select 2 columns (e.g. A and B) | ||
- | * Enter " | ||
- | * Press **Ctrl + Shift + Enter** | ||
- | |||
- | This will get a list of data sources. The left column is the human readable data source name, the right is the "Fully Qualified Name" the unique identifier for the data source, this is what should be used in the data source parameter for other queries. | ||
- | |||
- | For some data sources the human readable and fully qualified names may be the same. | ||
- | |||
- | |||
- | === Get Tags === | ||
- | |||
- | You can get the data sources available to the Add-In by using the " | ||
- | |||
- | * Select a column (e.g. A) | ||
- | * Enter " | ||
- | |||
- | This takes 2 parameters: | ||
- | - The fully qualified name of the data source you would like to query (you can get this using the " | ||
- | - A search query or filter (e.g. " | ||
- | |||
- | * Press **Ctrl + Shift + Enter** to enter the formula | ||
- | |||
- | |||
- | This will return a list of tags on the specified data source that match the query. | ||
- | |||
- | **Examples** | ||
- | |||
- | // | ||
- | =GetTags(B1, | ||
- | |||
- | If the data source name is in cell B1 and filter in C1. This would select the first data source if you had a " | ||
- | |||
- | If no data sources are available you will get a "# | ||
- | |Authorized Applications]], | ||
- | |||
- | === Get Snapshot Data === | ||
- | |||
- | You can get the current value of a tag by using the " | ||
- | |||
- | It's also possible to have the timestamp and header row added to the result. If you wish to include these you will need to enter a range the correct size. There will be an extra column for the timestamps and an extra row for the header (i.e. to include headers and timestamps select a 2x2 range, just timestamps would be 2x1, just header would be 1x2 and just the value would be a single cell). | ||
- | |||
- | * Select a range the correct size (e.g. A1:B2) | ||
- | * Enter " | ||
- | |||
- | This takes 4 parameters: | ||
- | - The fully qualified name of the data source you would like to query (you can get this using the " | ||
- | - The name of the tag you want to query | ||
- | - Whether a time stamps column should be included | ||
- | - Whether a header row should be included | ||
- | |||
- | * Press **Ctrl + Shift + Enter** to enter the formula | ||
- | |||
- | This will return the current value of the given tag, with headers and timestamps as specified. | ||
- | |||
- | **Examples** | ||
- | |||
- | // | ||
- | |||
- | Would get the current value of " | ||
- | |||
- | // | ||
- | |||
- | Would get the current value of " | ||
- | |||
- | === Get Historical Data === | ||
- | |||
- | You can get the historical data by using the " | ||
- | |||
- | It's also possible to have the timestamp and header row added to the result. If you wish to include these you will need to enter a range the correct size. There will be an extra column for the timestamps and an extra row for the header. The number of rows required for the query will depend on the start and end dates as well as the interval, for example for 2 dates 31 days apart with an interval of " | ||
- | |||
- | * Select a range the correct size (e.g. A1:B31) | ||
- | * Enter " | ||
- | |||
- | This takes 8 parameters: | ||
- | - The fully qualified name of the data source you would like to query (you can get this using the " | ||
- | - The name of the tag you want to query | ||
- | - The start time for the query, this should be an Excel date (e.g. DATE(2018, 01, 01)) | ||
- | - The end time for the query, this should be an Excel date (e.g. DATE(2018, 02, 01)) | ||
- | - The sample interval, e.g. " | ||
- | - The data function, one of " | ||
- | - Whether a time stamps column should be included, | ||
- | - Whether a header row should be included | ||
- | |||
- | * Press **Ctrl + Shift + Enter** to enter the formula | ||
- | |||
- | This will return the current value of the given tag, with headers and timestamps as specified. | ||
- | |||
- | **Examples** | ||
- | |||
- | // | ||
- | |||
- | Would get the historical value of " | ||
excel.txt · Last modified: 2024/03/07 15:46 by su