The Doings of Raffles Haw. First published in 1891. Written by, as you recalled, Arthur Conan Doyle.
The basic plot is that Mr. Haw moves into a rural district called "Tamfield" and starts spending money like water. In due course, it becomes clear to the reader (and to some other characters) that the man has, in fact, found the secret that allows a man to transmute lead into gold at any time. Mr. Haw wishes to be quite generous about sharing the wealth with those who need it; he dreams of changing the world for the better.
As you said, there's a sad ending. It turns out that Raffles' unselfish goals are not as easy to achieve as he had hoped, and this shocks him. I'll treat the details as spoilers:
First, Mr. Haw discovers that his generosity is turning many of the
poorer people of the district into parasites who now just want to sit
idly by and wait for him to subsidize them, indefinitely, instead of
trying to do much of anything for themselves.
Second, some of the local people whom he has not financially assisted
are growing envious of those whom he has. (As in: "Why should I have
to keep working hard on my own farm, when my neighbor doesn't have to
do a lick of work because Mr. Haw feels sorry for him after he had
some bad luck?")
Third, his heart is broken when he discovers that a local lady, having
promised to marry him, had never bothered to break off her previous
engagement to another man. That man suddenly comes home on leave from
the Royal Navy and expects his fiancee to marry him immediately before
he has to go back to his ship. Raffles Haw had not even known this
other engagement existed. He goes home, destroys the apparatus that turned
lead into gold, and then dies (allegedly of a broken heart, rather
than suicide), leaving no record of how his transmutation process
In my first paragraph, I linked the novel's title to a page on the Project Gutenberg website which will allow you to download the full text of the novel in any of a variety of formats. (Since the copyright has long since expired, at least in the USA. I'm not sure about the copyright status in the United Kingdom or elsewhere.)