Hi ! I am currently designing my own stock excel/google sheets but I can't seem to find a way to pull KLSE data into it. Is there any way for me to proceed?

Thanks

I think it's not possible to obtain data on Google Sheets using Google Finance formula because it is not supported.
You might be able to if you have a paid database and use an add-on.

On Excel, it is possible to pull data from websites.
Example here.
So you may be able to pull from websites that has Malaysian closing price.

Example of pulling from klse.i3investor.com




Do note that most websites do not give live rates and are often delayed.
Therefore, it's not useful for daily trading.

I just tried using =importxml in google sheet, it works!

But I choose to scrape the price from Bloomberg instead, just a personal preference. Google limit the formula auto update in sheet at once per hour interval if use too many of the formula in one sheet, so not suitable for day trading too. Though it’s great to have the sheet as a tracker to check at night after work

Thanks for sharing. But how do you get the Xpath?
I tried but I got an error.



Took the Xpath from here. There's Xpath and full Xpath

I did this



D7 = the cell that I put the Bloomberg url

Then I get a column of the url I want to track, and copy paste the =importxml formula

This post has been edited by Intimidated: Mar 3 2020, 10:17 PM

Nice! But do you know how to get the code? So can use for other websites too
Edit: Wait. I think the picture I posted has the answer lol.
Edit 2: Oops doesn't work. Ah well.

This post has been edited by Yggdrasil: Mar 3 2020, 10:28 PM

Ya ya i think the formula only works with things like

I also trial and error lol

This post has been edited by Intimidated: Mar 3 2020, 10:26 PM

I think I found it. For example, klse.i3investor.com



1. Input the formula like usual: =importxml("https://klse.i3investor.com/servlets/stk/annrep/6084.jsp",
2. Use inspect element to highlight the data.

As you can see, the code starts with "td". So,

3. 0.38 which is the data we need is next to "class", so

4. Fill in with the remaining code. So,

5. Final code:



6. Results

Oh I just tried on a stock that gained instead of loss

The class will be ‘up big16’ instead

So maybe can do like

IFERROR(importxml_with_up big16, importxml_with_down big16)

Wow learnt something new today. Thanks for sharing!

Mine still have error of #NAME?

Any idea how to solve it ?

Which website and which stock?
Post your formula here

SUNREIT, from KLSE i3investor

=importxml("https://klse.i3investor.com/servlets/stk/5176.jsp","//tdclass='up big16'")

Hmm. klse.i3investor.com code is unstable as it changes everytime.

Use Bloomberg instead. E.g. for Sunreit:

Why need to use excel? Plenty of established free or by subscription tool available. You want to be successful or just having fancy excel sheet is the goal?

Still the same problem wor. Am i missing out anything? need to enable any developer tools etc. ? I just copied your code into my excel formula then #NAME?

The code is for Sheets. For Excel, you need to import the data from the link.

Ah my bad for not reading it. Got it (on sheets).

Thanks!

bmwcaddy

Rough guide how to get the data. Clean up later if you want.

Excel
1. Copy URL.


2. In Excel, go to Data.


3. Paste link.



4. Navigate to the info you want. Easy way is to just import the whole table and hide the sheet we don't want. Click load.



5. It will appear like this.



6. Clean up if you want. My putting a master sheet with what you want to see and the other data in another sheet like this.



7. Click refresh all when you want to get latest data.

Thanks for the detailed steps. But my problem seems to be stuck at step 2: My excel 2016 cant run the script (error running) of the website, causing it to crash whenever i past any web link

Some people just prefer a simple spreadsheet tracker that doesn’t require additional login/software like me

I like to have one column for the actual amount paid (including fees) for the shares, one for how many shares I have, and one for the latest price (which I have now automated with the importxml formula!). Then some other fancy formatting like scale weightage conditional color formatting, blabla.

So it gives me a quick glance how much the real gain/loss is without me logging into another website or software. I already use Google Drive extensively for work

EDITED: just realised I can scrape fund prices from Bloomberg for my FSM/other unit trust portfolios as well now I can have a one-sheet tracker to tell me how are the investments doing, don’t have to login here there anymore

This post has been edited by Intimidated: Mar 4 2020, 04:55 PM

Hi ! I actually am more comfy using Google Sheets/ Excel tbh. What other free or subscription tool do you know? I quite cheap skate cause I am still a student not willing to pay subscription

Can you explain the IFERROR part?

Thanks!

Thank you very much for the tutorial.
It works!!!

The formula will be long and complicated. Just use Bloomberg for Sheets. Solve the problem.

it's just a normal Excel formula. you can understand from this link https://www.excel-easy.com/examples/iferror.html
once understand then you can apply in google sheets as well

What tool would you suggest?

This is quite useful. And I realize the naming convention of KLSE URL is the stock code, so can use the concatenate(or &) formula and only need to amend the stock code.
i.e. ="https://klse.i3investor.com/servlets/stk/"&"Stock Code&".jsp"

Hi,
how to get the price for day high only?

Thanks

Try this

I tried this
But it will fetch 52 weeks price also. The code is identical. But i dont know how to limit to day range high price only.

It fetched the day high for me. Not sure why it doesn't work for you.

52 weeks 1.98 will come up at bottom....


Attached thumbnail(s)

Just hide the cell. Can't help it I think because both share the same code.

Noted. Thanks alot

The stock price from xml will not update automatically?

Should auto update but you may need to refresh the page.

Hey bro, I trying to follow what you guys done here with KLSE Screener. I use IMPORTHTML to get the table I want but again, it is abit tricky to import the price

This is the code : =IMPORTXML("https://www.klsescreener.com/v2/stocks/view/"A2,"//spanid='price'")

A2 is the cell/stock code, and the remaining is modified according to what you guys done, it is originally from KLSE Screener element for price display.
But at the end it does not show anything, just blanked. Could you please lookup for me, please I feeling like just one small step away from success

A2 cell:

CODE:

P.S.: To others, stop asking me anymore Tired of this. Just trial and error please.

This post has been edited by Yggdrasil: Apr 30 2020, 09:19 AM

Excellent! I have the same desire to have all my data on a page and with my own analysis calculations.

Currently, I am importing historical data from different web sites, cut and paste the data into a spreadsheet for my calculations and graphing. Find that tedious. So an auto data gathering spreadsheet will be my dream.

I hope that you can help when I go back to work on this spreadsheet.

Thanks.

P/S Get the latest data or also historical data from your method?

hi can someone help me out here. been trying and trying but with no success. can someone point out where i am wrong?

=importxml("https://www.klsescreener.com/v2/stocks/view/5238","//*(@id=price_header)")

thanks in advance

This post has been edited by gegambo: Jun 2 2020, 06:12 PM

Found this today. Excellent way to get stock data via your Excel!

https://www.youtube.com/embed/xzOvM164H6M?rel=0

But need paid Excel 365 for the latest functions, I think.

Sadly bursa not supported https://support.microsoft.com/en-us/office/...ea-c5a6c8e787e6

Hi, anyone with success using =GOOGLEFINANCE() in Google Spreadsheet to pull KLSE stock prices?

Not sure why but the Bloomberg formula that works well for the past few months is now showing error

Because bloomberg, along with many other sites with stock prices are one-by-one blocking us from fetching data. You've to find other new websites to fetch prices from.

I think Google doesn't have permission to pull from Malaysia exchange.
It can't pull from Japan exchange too. You need to pull from other websites.

Yeah not sure why but I managed to pull from investing.com

Your code is wrong. Here's the correct one.


It's not that hard to learn.

BTW TO OTHERS... STOP ASKING ANYMORE..
If error means your code wrong or website cannot pull.

For those interested, can also use my spreadsheet. I published it for free initially for fundsupermart users but over time I've moved on to other vehicles (incl. Malaysia / Foreign stocks)

The spreadsheet might be quite heavy though.

Links in my signature or read more here: https://forum.lowyat.net/index.php?showtopi...&#entry98487565

You have a great thing going there, though I'm wondering why don't you move to Google Sheets, since Google Sheets is superior in so many ways.

For instance, my portfolio file on google sheets is able to save my end-of-day portfolio value, without having to open the file at all. Google scripts can be automated to be triggered on a time basis, without requiring one to open the file on a daily basis and pressing macro buttons. So I get a summary of how my portfolio of stocks on Bursa, SGX and HKEX have evolved over the last 3+ years as per the chart below. All without pressing buttons and running macros. I only have to key into my sheet my trades and dividends received, with google sheets taking care of everything else.





This post has been edited by Havoc Knightmare: Oct 19 2020, 08:12 PM

Dang, that's actually pretty neat! Personally I thought about it a few times but was too lazy to re-do everything again on Google Sheets and that kinda held me back

Maybe I should really spend some time and look into it My snapshot is only as good as the times I remember to refresh it There's some obvious "holes" on certain month when I forgot to even open the sheet

Yeah, with google sheets you will never have to open the sheet just to run macros. You just need to check in every now and then to correct any errors since google sheets doesn't fetch data correctly occasionally and you end up with #error cell outputs.

My current sheet is derived from this Singaporean blogger's work since I have little knowledge in programming languages - https://investmentmoats.com/StockPortfolioT...menttracker.php

Perhaps you can use his sheet as a blueprint to work with.

Wow that dedication!

I manage to use klse.i3investor.com to pull the prices but as some has mentioned the formula keeps on changing sometimes it’s up,sometimes it’s down

Solution already mentioned here:
https://forum.lowyat.net/index.php?showtopi...post&p=95692476

Use IFERROR( ...link with up... , ...link with down...)
This means if Google fetch the link with up and it's an error, it will revert to link with down.
Disadvantage is it's a long formula.

Hi there, i wondering is there any method or insert any code to make it auto refresh within few second/minute instead of manual refresh it?

You can easily Google this..

I am still having trouble doing this. Could you be so kind to help me out? It keeps showing N/A. And it says content imported is empty.

https://pictr.com/images/2020/10/30/7MOy35.png
https://pictr.com/images/2020/10/30/7MOXjV.png
https://pictr.com/images/2020/10/30/7MOKpv.md.png

N/A because Bloomberg website doesn't allow "scrapping" figures or Google's website having problem retrieving.
Extract from klsescreener instead.

hihi, i had created a webpage mainly to cater this google sheet problem. Feel free to test it out and let me know any feedbacks =)

www.mybuku555.com

May I know the code to pull out the 52w from klsescreener?

Below code is to import the price.
If I want to import the 52w info, why to do it?
Thank you in advance.

https://pictr.com/images/2021/10/23/BL49Z6.md.jpg

what a good sharing on this.

Like this to keep it simple.

Hi Bro & Sis, I wish to get the price different with the (xxx%) beside it, what is the code?

Hi buddies, I am import the data from klsescreener.
When i import that price different everyday, it comes with % up or down. How to remove it.

How to amend to to remove the % up or down that beside the price different.
Thanks in advance!

This option was good to use for 2 years. Today i3 upgraded their site and can't pluck the latest price from there.
Thank you Yggdrasil for showing us this option.

Does anyone have an updated method to pull price data for KLSE stocks? The old method suggested in this thread no longer works after i3investor updated their website

You create a portfolio like Philips Farms in I3.
https://klse.i3investor.com/servlets/pfs/120720.jsp

Then you import the data from I3.

How do you go about doing this?

1. Register a user id in i3.
2. Sign in and create a portfolio with the stocks you want to get the price.
3. Make that portfolio a Transaction Type and a Shared Access Type.
4. You import the link of that portfolio to your Excel spreadsheet where you can pluck the price of the stock you created.

If you're using Google Sheets

Here's the new code :

Hi,
I want to extract Debt to Cash ratio from klsescreener into my Google sheet, tried many methods but still unsuccessful, anyone can help ?

Hello, have been using i3investor to pull bursa prices for sheets, but recently the prices are no longer updated.

Anyone has the most recent code? Or any other ways to pull live prices on sheets?

Could you share the code you were using? I had the same problem a few days ago, I just modified the url a bit and it worked. Yahoo however is not working, page is down.

Hey I was using the code you shared in 2022. But i just checked back sheets just now and it was working again. Was down for the past few days thought need a new code. Thanks again

Hello, looks like the code has been down for the past few weeks. Anyone has the new code to pull recent prices from i3 website or any other sites?

I cannot get it to work either with KLSE stocks. Tried various methods. It stopped working for me the past week. US stock still ok using Google Finance.

If anyone found a way to extract KLSE stock price in Google Sheets kindly share here.

same here bro. Not able to scrape KLSE stock price into Google Sheet. Appreciate anyone able to do it to share the code.
Thanks in advance.

Eg... for the green Telco provider..



This post has been edited by jegan80: Mar 4 2025, 09:50 AM

Thanks man. This works for my klse stocks. Cheers!

Anybody got the latest code ?

Recently I wanted to create a simple spreadsheets for certain HKEx stocks- daily opening, closing, high, low and volume. Ask deepseek and am astounded it solved my problem in less than 10 minutes. Link google sheet with google finance and from there expanded the spreadsheet to include 52w h&l, price difference and % difference.

Try using deepseek, I find it easier and faster to get answers rather than chatgpt.

This works. Thank you ma man