diff options
-rw-r--r-- | website/content/excel/data-connectors.md | 2 | ||||
-rw-r--r-- | website/content/excel/data-slicer.md | 61 | ||||
-rw-r--r-- | website/content/excel/functions.json | 33 | ||||
-rw-r--r-- | website/generate_excel_markdown.py | 18 |
4 files changed, 105 insertions, 9 deletions
diff --git a/website/content/excel/data-connectors.md b/website/content/excel/data-connectors.md index 07656ec36e0..24bb98376b3 100644 --- a/website/content/excel/data-connectors.md +++ b/website/content/excel/data-connectors.md @@ -37,7 +37,7 @@ If your backend supports it is possible to pass a symbol, date or other optional ``` :::tip -The easiest way to pass optional parameters is write them into cells and reference them in the function. For example, `=OBB.BYOD(...,A1:B2)` where A1 contains "param1", B1 "value1", A2 "param2", B2 "value2" and so on. +The easiest way to pass optional parameters is to write them into cells and reference them in the function. For example, `=OBB.BYOD(...,A1:B2)` where A1 contains "param1", B1 "value1", A2 "param2", B2 "value2" and so on. ::: #### Additional notes diff --git a/website/content/excel/data-slicer.md b/website/content/excel/data-slicer.md new file mode 100644 index 00000000000..a1cc27ba7fd --- /dev/null +++ b/website/content/excel/data-slicer.md @@ -0,0 +1,61 @@ +--- +title: Data slicer +sidebar_position: 5 +description: Slice Excel ranges by label or index +keywords: +- Microsoft Excel +- Add-in +- Advanced +- Slice data +- Data slicer +- Get specific fields +--- + +<!-- markdownlint-disable MD033 --> +import HeadTitle from '@site/src/components/General/HeadTitle.tsx'; + +<HeadTitle title="Data slicer | OpenBB Add-in for Excel Docs" /> + +To help you slice parts of data, we provide the [OBB.GET](https://docs.openbb.co/excel/reference/get) function. This function allows to slice rows, columns or range subsets. It is useful to extract specific fields from the `OBB.` custom functions. Data can be sliced by label or index. + +### Example + +- Suppose you called an `OBB.` function and it returned the following data at cells A1:D3: + +| period_ending | revenue | cost_of_revenue | gross_profit | +|---|---|---|---|---| +| 2023/09/30 | 383 285 000 000.00 | 214 137 000 000.00 | 169 148 000 000.00 | +| 2022/09/24 | 394 328 000 000.00 | 223 546 000 000.00 | 170 782 000 000.00 | +| 2021/09/25 | 365 817 000 000.00 | 212 981 000 000.00 | 152 836 000 000.00 | + +- Slicing a single row: + +```excel +=OBB.GET(A1:D3,DATE(2023,9,30)) +``` + +:::note +When passing date labels make sure to use the format `YYYY/MM/DD` or refer to a cell range containing Excel date format - DATE(year,month,day). +::: + +- Slicing a single column: + +```excel +=OBB.GET(A1:D3,,"revenue") +``` + +- Slicing by index: + +```excel +=OBB.GET(A1:D3,2,3) +``` + +- Slicing multiple rows and columns: + +```excel +=OBB.GET(A1:D3,{"2023/09/30","2021/09/25"},{"cost_of_revenue","gross_profit"}) +``` + +:::tip +The easiest way to pass ranges is to write them into cells and reference them in the function. For example, `=OBB.GET(...,A1:A2)` where A1 contains "item1", A2 "item2". +::: diff --git a/website/content/excel/functions.json b/website/content/excel/functions.json index 66f7a5fbdc1..70e99c565ea 100644 --- a/website/content/excel/functions.json +++ b/website/content/excel/functions.json @@ -1,6 +1,37 @@ { "functions": [ { + "description": "Slice data from a range.", + "helpUrl": "https://docs.openbb.co/excel/data-slicer", + "id": "GET", + "name": "GET", + "parameters": [ + { + "description": "Data to slice.", + "dimensionality": "matrix", + "name": "data", + "type": "any" + }, + { + "description": "Range of row labels or indices.", + "dimensionality": "matrix", + "name": "row", + "optional": true, + "type": "any" + }, + { + "description": "Range of column labels or indices.", + "dimensionality": "matrix", + "name": "column", + "optional": true, + "type": "any" + } + ], + "result": { + "dimensionality": "matrix" + } + }, + { "description": "Bring Your Own Data features from OpenBB Terminal Pro.", "helpUrl": "https://docs.openbb.co/excel/data-connectors", "id": "BYOD", @@ -4403,4 +4434,4 @@ } } ] -}
\ No newline at end of file +} diff --git a/website/generate_excel_markdown.py b/website/generate_excel_markdown.py index b86e5bf230c..49d414802c6 100644 --- a/website/generate_excel_markdown.py +++ b/website/generate_excel_markdown.py @@ -33,8 +33,14 @@ class CommandLib: # These examples will be generated in the core, but we keep them here meanwhile EXAMPLE_PARAMS: Dict[str, Dict] = { + "/get": { + "data": '{"a","b","c";"d","e","f"}', + "row": '"d"', + "column": '"c"', + }, "/byod": { "widget": '"widget_name"', + "backend": '"backend_name"', }, "crypto": { "symbol": '"BTCUSD"', @@ -247,11 +253,9 @@ class CommandLib: standard_eg += f"{p_value}{sep}" standard_eg = standard_eg.strip(f"{sep} ") + ")" - if required_eg == standard_eg: - return {"A. Required": required_eg} - # Uncomment to add standard examples - # return {"A. Required": required_eg, "B. Standard": standard_eg} - return {"A. Required": required_eg} + if cmd in ("/get", "/byod"): + return {"Required": required_eg, "Standard": standard_eg} + return {"Required": required_eg} def get_info(self, cmd: str) -> Dict[str, Any]: """Get the info for a command.""" @@ -337,7 +341,7 @@ class Editor: def get_syntax() -> str: if sig := cmd_info["signature"]: syntax = "## Syntax\n\n" - syntax += f"```{self.interface} wordwrap\n" + syntax += f"```{self.interface}\n" syntax += f"{sig}\n" syntax += "```\n\n" return syntax @@ -381,7 +385,7 @@ class Editor: examples = "### Example\n\n" for _, v in cmd_examples.items(): # examples += f"### {k}\n\n" - examples += f"```{self.interface} wordwrap\n" + examples += f"```{self.interface}\n" examples += f"{v}\n" examples += "```\n\n" |